0

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.

Improve this question
1
  • 1
    How do you set your conditional formatting? It works fine for me Commented Mar 12, 2014 at 3:21

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.