Fix problem with relative reference formulas in conditional formatting
At present, if I want to compare 2 values in a row, I can easily use the conditional formatting feature to choose from which value is the highest, and change the colour of the cell for example.
The problem occurs when I want to apply that to each row.
At present, if I select the cells and use format painter, I can apply it to the 2 cells in the row below, and the conditional formatting formula is applied perfectly (relative to the 2 new cells).
However, if I want to apply that all rows relatively, select the cells with the format painter and copy the formatting, to apply it to all the rows. What happens is that the cells in each row are not compared in pairs per row, but compared across the cells in all the rows.
I have tried to fix this by taking the initial formula in the conditional formatting and removing the $ before the row number. When I save the rule, Excel revert the row reference back to absolute ($A$2 etc).
So currently there is no way to apply this formatting to all the rows in the sheet without doing them all individually. We have 4,000 rows.
I have had a remote session on this to see if I'd missed something, and the support agent concurred that this was a limitation in Excel, and directed me here.