Conditional Formatting won't work in excel?

Question

I have inconsistent success with using conditional formatting in excel. I am using this in order to highlight ever other order. The orders are sequential and have multiple lines. The goal is to have a highlight rule that highlights when the "Highlight Rule" column is odd. This column uses the formula =if(C2=C1,C1,C1+1)

However this doesn't always work. Sometimes it just refuses to highlight anything at all. I have tested to make sure excel sees the cells as even and odd as needed but I am apparently doing something wrong within the rule itself.

Please help, I feel so dumb that I can't get this to consistently work. I must be doing something different when it works vs when it doesn't but to my eyes I see nothing, it looks exactly the same.

Edit: Added screenshot with rows/columns. The highlight rule would look at column C for alternating highlights.

Edit 2:

Here is a redacted exact screenshot of my spreadsheet. You can still see column AO reflects the highlighting on the entire sheet:

FINAL EDIT Ian below was able to find the obvious thing I didn't think to check. I had a fill overriding my conditional formatting

Note: Highlight rule formula should read more like "=if(A2=A1, C1, C1+1)" in line with the screenshot example. — Mel
Trick is to write the formula as if you are in the top-left cell of the range that has the CF rule applied AND to use $ signs just as if you would want to copy that formula to other cells. SO if your range of cells with CFstarts on row 3, then row 3 is the anchor row on which you should base your cell references. — jkpieterse
@jkpieterse thank you! Unfortunately this solution/error only causes the highlight rule to be off by a few lines. The problem is that it highlights nothing at all. — Mel
@ian0411 Added! Thank you! I suppose I should have included those in the first place. — Mel
I cannot reproduce your error. Can you give an example of some actual values for which it does not work as expected? — Ben I.

JustAPup JustAPup · Accepted Answer · 2017-10-23T16:55:12

I think the issue lies in your formatting. You haven't specified that the format is highlight (e.g., yellow). I'm defining my formatting as below, and it works for me.

I notice that the Apply button is still enabled in your screenshot. Have you actually applied this rule?

Update: also try making the criteria field a range, i.e., =ISODD($AO1:$AO1000)

Conditional Formatting won't work in excel?

1 Answers