The first place to start is the board itself to see what that supports. If it runs DDR2 800 as the standard the fsb will be lowered when running 667mhz memory. For 1066 if supported will still see the 800 memory's speed and run at the same fsb as the 800mhz memory would. The "memory standard" is the actual fastest stock speed of memory while a board may easily run a faster speed of memory like seeing 1066 on an 800 model.
For most boards now the value type memory will go right in and be seen and run at the same speed while performance memory with lower latencies/faster timings often require you to manually enter those in the dram timing section of the bios. That can also included manually setting the speed rather then leaving it set on auto there.
Back to the board specifications to see what cpu is supported according to socket type seen there. On Intel Socket 775 boards many see Celerons, P4s, and Core 3 Duos while others see P4s, Core 2 Duos, Core 2 Extremes. Some also still include the Celerons as well.