Hello all,

Thanks for stopping by. I applied conditional formatting to 20k+ rows using custom rules (formulas). The conditional formatting is applied but it does not work unless I retype the data in the cells. Does anyone know why this is happening? I have thousands of rows and I can't retype everything.



I tried copying and pasting the data back in the spreadsheet but that does not work.

Your assistance is appreciated!

Rey Krauss

Hi
Can you post a copy of your formula and Conditional formatting rule(s) so I can check them on my machine.
Tony

Hello Tony,

The conditional formatting formula I'm using is: =IF(AND(OR(C4="PRO",C4="SER"),H4=1,I4=0),TRUE)

The data already existed when I applied the conditional formatting down to all rows. I also tried creating the conditional formatting first and then pasting the data but that does not work either. I know the formula works because if I type over the cells, the row highlightes. It just does not highlight if I paste the data or if the conditional formatting is applied to data already in the cells.

Hope this helps!!

Rey

Hi Rey,

Your calculations may be set to manual. If that's the case then change them to automatic.

For reference, the IF(condition, TRUE) isn't required in your formula, since AND() returns TRUE/FALSE anyway. You could simplify to either of these:

=AND(OR(C4="PRO",C4="SER"),H4=1,I4=0)

=AND(OR(C4={"PRO","SER"}),H4=1,I4=0)

__________________
Colin

RAD Excel Blog