Forum Discussion
Conditional Formatting
Hello, I am developing a conditional formatting rule that allows me to enter a specific text value, and if it matches the content of another cell, that cell will be highlighted in green. However, instead of highlighting only the matching cell, the entire selected range is being highlighted each time I enter a text value.
4 Replies
Essentially, the format now works as intended, but is there a way to leave the text highlighted as if I intend to check it off a list by typing its name?
This is the rule for conditional formatting in the attached sample file.
=E2=$N$2This is the "applies to" range.
=$E$2:$K$15All exactly matching cells within the applies to range are highlighted.
Hello, I appreciate your previous assistance. However, I’m encountering an issue where, after removing the typed text from the source, the highlighting disappears. Is there a way to retain the previous highlights to simulate a checklist?
The purpose of this formula is to allow me to enter a Pokémon name in the "Highlighter" section (cell A3), which then highlights the corresponding name in the checklist.
Sorry for the late reply. It seems that either i didn't receive a notification of your reply on Oct 11, 2025 or i was missing something.
Retaining the previous highlights can be done with VBA. The VBA worksheet change event code posted below returns the intended result in my sample file. All entries in range B2:AA157 that match the value in cell A3 are highlighted and the highlight is retained if the A3 value is cleared or changed. For some reason i can't attach my sample file currently. Without VBA this isn't possible as far as i know.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range Set rng = Range("B2:AA157") If Not Intersect(Target, Range("A3")) Is Nothing Then For Each cell In rng If cell.Value = "" Then Else If cell.Interior.ColorIndex = 7 Then Else If cell.Value = Target.Value Then cell.Interior.ColorIndex = 7 Else End If End If End If Next Else End If End Sub
