Excel Filter: Conditional Formatting Problem

January 16, 2018 9:54 AM

Something is going wonky with my conditional formatting in a simple (I think) Excel sheet. I want cells in column D to turn green if their value is less than the value in the column C. No problem, except when I paste values into column B (column C is a running total working off of info in B), the conditional formatting stops working and turns cells green for awhile after it should stop.

This doesn't happen when I type the values, only when the values are pasted. However, pasting should ideally be an option. I can't find an example of this problem elsewhere online.

Screenshot of my rule
Screenshot of what is happening in my sheet

Column A is a plain value
Column B is where I add values (hopefully by pasting!)
Column C is a formula that adds the new citation count to the last to create a running total (eg, C3=C2+B3)
Column D is a formula that squares the value in column A (eg D3=POWER(A3,2)

Let me know if you need more info. Thanks for your keen eyes and Excel expertise.

posted by LKWorking to Computers & Internet (4 answers total)

Best answer: Apologies in advance if this is stuff you already know, but.....Your conditional formatting rule doesn't say that the value should be less than the adjacent value, it says the value should be less than the value in cell C192. The $ signs prevent excel from updating the both the column and row values. Could that be the problem?
posted by Ausamor at 9:57 AM on January 16, 2018 [2 favorites]

Does the problem keep happening if you paste special -> Values instead of just pasting? Conditional formatting will be pulled along with a regular paste if you're copying from another excel sheet.
posted by Uncle at 10:17 AM on January 16, 2018

Response by poster: Ah, I think Ausamor found something at the heart of it. When I updated the value in the rule to "=$C2" that seemed to fix the issue. I'm still a little unclear on what was going on since I don't think that accounts for the formatting stopping at a seemingly arbitrary place. However, your direction helped solve the issue, so thank you!
posted by LKWorking at 10:25 AM on January 16, 2018

Best answer: I'm still a little unclear on what was going on since I don't think that accounts for the formatting stopping at a seemingly arbitrary place. However, your direction helped solve the issue, so thank you!

What is the value of cell C192? Is it greater than 4489 and less than or equal to 4624? Having the $ in front of 192 meant every cell in your conditional formatting range was comparing itself to C192. When you changed it to $C2, it means your top cell compared to C2, second cell down compared to C3, third cell down compared to C4, etc.
posted by noneuclidean at 10:48 AM on January 16, 2018 [2 favorites]