Hi all, I will appreciate help with this please.
I have the following vba code working where for any text put in cell M2, it is searched in the range M5:M55 and anywhere the text is found, it is highlighted.
I need help to improve this where the search criteria, currently using cell M2, is expanded to include value of cells N2 and O2.
The search range should remain M5:M55
Thanks
I have the following vba code working where for any text put in cell M2, it is searched in the range M5:M55 and anywhere the text is found, it is highlighted.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
SelectAndChange (Target)
End Sub
Private Sub SelectAndChange(strValue As String)
Dim rngCell As Range
Dim rngRange As Range
Dim strLookFor As String
Dim arrChar As Variant
Dim lngCounter As Long
If strValue = vbNullString Then Exit Sub
Application.EnableEvents = False
Set rngRange = Range("M5:M555")
rngRange.Font.Color = vbBlack
strLookFor = Range("M2").Value
For Each rngCell In rngRange
For lngCounter = 1 To Len(rngCell) - Len(strLookFor) + 1
If Mid(rngCell, lngCounter, Len(strLookFor)) = strLookFor Then
rngCell.Characters(lngCounter, Len(strLookFor)).Font.Color = vbRed
End If
Next lngCounter
Next rngCell
Application.EnableEvents = True
End Sub
The search range should remain M5:M55
Thanks