At the end of BOBW part 1, when Riker said, "Fire..." and we get the "To be continued..." i was like, "F-yeah! Blast the remaining hair off of that proper captain-turned-Borg."
When Worf gently applies the killing blow on Duras just as Picard protests. Its nice when a punk-ass gets what he deserves.
It was a "heck yeah" moment for me in "The Die is Cast" when a romulan personnel said there were 144 Jem'Hadar fighters swarming towards their measly 20 starships. Yes, i was rooting for the Founders.
As a "heck yeah" and, at the same time, "oh shiiiiite" moment, i have to give it to near the end of "The Jem'Hadar" episode. Where a Jem'Hadar attack fighter rammed the USS Odyssey (Galaxy-class). That was an awesome moment which gave a hint on how the Dominion War would be. Epic!