Share via

How to highlight duplicates only if another cell contains certain text?

Anonymous
2024-02-27T19:23:41+00:00

So in column D I have a list of names from row 2-60 and will get longer as I add more in. In column H I have TRUE and FALSE depending on a check box in column A. I only want duplicates in column D to have red text if column H is FALSE. So if it has TRUE, leave it alone. And because I’m putting entries in nearly everyday I need it to update or if I check the box, it changes to TRUE, then it recognizes and changes as well.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-02-27T19:28:42+00:00

    Select D2:D1000 or however far you want. D2 should be the active cell in the selection.

    Select these columns; I'll assume that the active cell is in column A.
    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =AND($D2<>"", COUNTIF($D$2:$D$1000, $D2)>1, $H2=FALSE)

    Click Format...
    Activate the Font tab.
    Select red as text color.
    Click OK, then click OK again.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-02-28T17:46:28+00:00

    This worked, thank you so much!!

    0 comments