Here's the straight skinny -
You can always use faster RAM with any modern CPU/MOBO setup - you're just likely to have to set it up manually in the BIOS to take advantage of it. Pretty much all DDR2 ram is actually DDR2/800; they 'speed-bin' it, i.e., test and select the sticks that will work at either lower (faster) latencies, or higher (faster) speeds, or both, and sell it at a premium as 2/1066, 2/1200, and so on. JEDEC spec'd RAM has a little EEPROM chip in it that stores the set-up information/tables for running it at 800 at various FSB (Front System Bus) speeds - has the preferred memory multiplier and timing info - this is called an SPD (Serial Presence Detect) just to confuse us; faster, higher rated sticks may (but don't necessarily) contain another set of tables (called an EPP - this one makes sense - Extended Performance Profile) that will tell the BIOS what multiplier/latncies to use at its higher rated speed - BUT - not all BIOS are created equal: some will read this EPP automatically, and set the RAM at the higher speed; some will require intervention (on a lot of GBs, it's "Load Optimized Defaults" [but, to keep it more confusing - not all BIOS with the "Load Optimized Defaults" fuction actually use it to set the EPP]), and some just plainly don't know the EPP exists (if it does) and you have to set the higher speed manually!
Now, you have control over the basic system clock (I'm going to cal it B_CLK), once you start manually timing the MOBO through the BIOS. B_CLK times four is your FSB (once again, Front System Bus); B_CLK times your memory multiplier is your DRAM rate; B_CLK times your CPU's multiplier is your CPU frequency.
Examples: if you set your system clock to 333, you will need a 2.4 memory multiplier (333 x 2.4 = 799.blahblahblah) to run your RAM at 800, and if the CPU multiplier is, say, 8.5, you will get a CPU clock of 2.83GHz; at that same B_CLK you would need a memory multiplier of 3.2 (3.2 x 333 = 1065.6) to take advantage of 1066 RAM. Now, lots of CPUs that are rated at a 1333 nominal FSB will run a lot faster, sometimes with a little more 'oomph' from a voltage increase; for example, I run a Q9550 that is rated at 1333 FSB (333 B_CLK) times an eight point five multiplier, for a 2.83GHz speed. It will comfortably run with the B_CLK well over 450 - and here's where faster RAM comes in. The smallest RAM multiplier available from a MCH (Memory Control Hub - or 'NorthBridge') is 2.0, but, with a 2.0 multiplier, that means at a 450 clock, your RAM will need to run at 900 (again, 450 B_CLK x 2 = 900), which most 800 RAM just won't do! This is referred to as a 'RAM limited bus', meaning the CPU can't run a B_CLK any higher than (roughly) half the RAM's available speed - and thus, the need for faster RAM. Mind you, this only applies if you both can, and intend to, run your FSB above 1600 (once again, a B_CLK of 400+ times 4 gives you a 1600+ FSB)...
To further complicate matters, people often misunderstand the actual quantitative speed improvements inherent in faster ram... Here's the mistake: 1066 is 33% higher than 800 ([1066-800]/800 = 266/800 = .33), so 1066 RAM must be a third faster than 800, right? Not so! You have to figure in latencies. Most 800 will run at 4-4-4-12, while most 1066 is rated at 5-5-5-15, or, even worse, 5-5-5-18. Here's how to appraise the situation in reality: at 800 MHz, a RAM bus cycle is 1.25 μSec long (1000/800); at 1066 (1000/1066), it is roughly .938 μSec long - so, with an 800 stick at a 4 average latency, a RAM bus transaction takes 1.25 (cycle time) times 4 (latency), or 5μSec, while at 1066 it is .938 (cycle time) times 5 (latency), for a transaction time of (roughly) 4.7μSec - so you see, by going to nominally 33% faster RAM, you actually gain three tenths of a μSec per transaction - .3 (transaction gain) over 5(transaction total) = .06, for a real-world improvement of 6%
My experience with 'GB-friendliness' by manufacturer has been: mushkin - GBs love mushkin, but it's pricey, and the speed selection is limited; G.Skill - works well, has a functional EPP, and will usually also run at 'auto' settings, unless you run four sticks; OCZ - likewise; Kingston, Crucial, & Corsair - seem to account for most of the problems I see here with RAM (wich, of course, could possibly be due to the fact that more people buy them, as they're generally cheap), with Crucial having a few times had problems with apparent 'degrading' over time, i.e., a previously working OC simply 'goes bad', and MemTest86+ shows it to be RAM...