Conditional Formatting not working on new sheet

Occasional Visitor



This one has me stumped. I was using the same formula on another sheet and was working with 98% success. New sheet, cleared all conditional formatting rules and the re-entered the formula but the formatting is still not working. I am looking for a simple greater than (green), less than (red) colour split for data in two columns. In my previous sheet it was working ok, with the exception of some cells with identical values. This sheet however just doesn't want to work.


I've attached two photos, one of a snap shot of some of the data where it isn't working and the conditional rules I have.


As you can see, one area the values are identical (even copied the figure from the first column over to check) and it is highlighted red. The next row it should be blank (or red, I don't mine so much here). The following row should be red because 29.29 is obviously less than 34.53 but for whatever reason it just doesn't want to. I've tried editing the range to $F$3:$F$161 (the full column length) as well but it doesn't seem to make any difference to it.

Any help is greatly appreciated.

1 Reply

@SarahBevan Change the cel references in the rules from F3 and B3 to F1 and B1 or set the applied range to start in F3. Now, you are applying the rules starting on row 3 for the entire column F (thus starting in row 1). All the conditional formats are now shifted up two rows.