Conditional Formatting using VBA

Hi,

I am trying to do conditional formatting within a macro because the range changes each month when I run it...

However, it is throwing up a confusing issue...Here is the code

Please Login or Register  to view this content.

So, when I run the code and look at the conditional formatting on the sheet, instead of seeing =$S8=$Z$6, I see =$S9=$Z$6 and it highlights the row above the row I'm looking for?!

Any idea why this is?

I'm using Excel 2007. I got a colleague to run it and he is using Excel 2010 and it seems to work fine for him...it's quite annoying and has been perplexing me for a number of hours now!!!!

Any help will be greatly appreciated.

Cheers,

E

there is a slight mistake in the code,

on the fifth line, there is:

Please Login or Register  to view this content.

It should be:

Please Login or Register  to view this content.

It's just getting the final row of column B