Forum Discussion
Conditional Formatting
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