The "DDR" in memory means "Double Data Rate." This means that for every clock cycle that the memory goes through, there is an instruction sent on the rise as well as the fall of the clock cycle. This means that for every actual clock cycle, there are 2 effective clock cycles. That's what the DDR2-XXX speed denotes. DDR2-533 is memory that is actually at 266Mhz, but is effectively 533Mhz. Same with DDR2-800; Actually 400Mhz, effectively 800Mhz. The memory you're talking about is more than likely DDR2-1066, since actual 1000Mhz memory does not exist yet. DDR2-1066 would be 1066Mhz effective and 533Mhz actual. The actual is what your system most often sees.
You memory is supposed to run at a 1:1 ratio with your FSB, which on Core 2 Duos is 266Mhz. For this reason, DDR2-533 memory is the memory needed to run a Core 2 Duo system without any overclocking at all. However, some motherboards will take higher-clocked memory and automatically set a ratio that runs the memory at the higher speed (DDR2-667, DDR2-800, DDR2-1066) even if the FSB is at 266Mhz. You can change this setting, and you will have to change it if you want to overclock.
On a Core 2 Duo E6700, with a 10x multiplier (266Mhz FSB x 10 = 2.66Ghz), the memory required for 4Ghz (a very unlikely overclock) is 400Mhz, or DDR2-800 memory. Any more that that, I see no use for. You could, ofcourse, set the memory to run at a higher speed than the FSB, but the premium for DDR2-1066 is not worth this effort.