Excel conditional formatting doesn't update when a formula returns a result

0
votes

I am trying to use the result of a formula to trigger conditional formatting.
I am using the following formula to check if the value in cell E2 appears anywhere in cells A2 to A9967. If it does appear the word "duplicate" appears (in my particular case this formula is in cell F2 so "duplicate' appears in F2).

=IF(COUNTIF($A$2:$A$9967,E2),"DUPLICATE","")

In this regard everything is fine, however, if I simply typed "duplicate" into F2 I have no problems getting conditional formatting to shade cell E2 a particular color BUT, I can't get the value returned from a formula (Duplicate) to trigger the conditional formatting.

The problem comes because I want to paste new values into the E column and have conditional formatting automatically shade those duplicates cells.

I enabled the developer mode and made sure EnableFormatConditionsCalculation is set to true.

Any assistance would be greatly appreciated.

1
excelvba
How do you set your conditional formatting? It works fine for me – sam092

1 Answers

1
votes

This worked for me to, so I believe you have one of these problems

  • The conditional formatting is set wrong

    • Mark cells E2:E9967
    • Open Conditional Formatting, Manage Rules
    • Press New Rule, and select "Use formula to determine which cells to format"
    • Enter the formula =$F2="Duplicate" (you always fill in the formula here as if you are filling in the formula for the top-left cell that you are making the conditional formatting for) (the $ before the F "locks" it to column F, even if column E will be moved)
    • Press OK

  • When pasting from other excel-range, the conditional formatting in the pasted cells are overwritten by the conditional formatting in the copied cells. To solve this, you can do Paste Special Values. Then all formatting stays.

  • The text in column F differs somehow from the condition in the conditional formatting. Make sure the formula in column F fills in "Duplicate" spelled exactly as how it is in the conditional formatting. Be aware that a space before or after makes them look different.