Conditional Formatting - Copy vs Cell References

Hi,

I have just registered to see if I can get some help on a problem in Excel 2010 that is DOING MY HEAD IN. I have been Googling for an hour and many people seem to have the same problem, but no matter what I try, I simply cannot get my conditional formatting to copy properly. My problem is in a large spreadsheet with many, many cells, but I am giving a very simplified example of the problem I have below to try to simplify it, as for many of the examples I Googled, it seems the question was simply not understood.

This is what I want to do: I have two ranges of cells: A1:A10 and B1:B10. I want to conditionally format the cells column B so that they highlight if they have a different value to the corresponding cell on the same row in column A. That is to say, if B1<>A1 highlight B1, if B2<>A2 highlight B2, if B3<>A3 highlight B3, and so on. Seems simple enough.

So I create a Conditional Formatting rule by selecting cell B1. I then click Conditional Formatting, Highlight Cells Rules, More Rules. In the pop up I then create the rule as follows: 'Format only cells with Cell Value not equal to =$A$1'; I set the format to bold red and click OK (Actually to select "$A$1", I just clicked on cell A1 and Excel added the "$" signs, I'll come on to this in a minute). So far so good. If I change the value of B1 so that it is different from A1, it highlights bold red. This is what I want.

Now I want to copy this conditional formatting to cells B2 through B10, so that if B2<>A2, B2 highlights bold red, if B3<>A3, B3 highlights bold red etc As mentioned. Sorry if I am repeating things but I want to be as clear as possible.

When I try to copy the formatting 'as is' using format painter (select cell B1, click Format Painter and painting into cells B2:B10), all the cells B2:B10 have conditional formatting that says highlight bold red if their values are different to $A$1. Now I'm not stupid, I know what the "$" means. So I remove all the conditional formatting and start again, this time making sure that the formatting for B1 says: 'Format only cells with Cell Value not equal to =A1'. In other words, I make sure the reference to A1 does not have any "$" signs, so that in theory it should be replaced by A2 in the formatting for B2, A3 in the formatting for B3, and so on. Again, the formatting for B1 works and when it has a different value to that in A1 it highlights red.

I copy the formatting from B1 again, using Format Painter as before to B2:B10. This is where the seemingly illogical problem occurs. I'm sure there's a good reason for it, but this time, if I look at the formatting for B2, it will highlight if its value is different from A2 (which is good) but all the other cells, B3:B10, also highlight if their value is different to B2! In other words, the reference to A1 in the formatting of B1, changes to A2 for the formatting of B2, but stays as A2 for B3 to B10!

When I Googled, some people said that to achieve this you have to use the "Use a formula to determine which cells to format" rule type. I have tried this (I'll spare you the details here), together with removing the "$" signs but I still get the same result no matter what I try. The only way the Format Painter seems to work is if you do one cell at a time, which is quicker that manually conditionally formatting each cell, but still a complete pain when you have thousands of cells! Double clicking the format painter makes it slightly faster but surely there is a way to do this??

Any ideas?

Thanks in advance,
Jules