non-case sensitive search using VBA

[ferrigeu]

i have having sort of an "off day"... i just cant seem to figure out how to add code to make the search non-case sensitive.
it all is good if i by chance type the search criteria in the correct case. if not, it will not be found....
below is my code
ideas pls?

Sub HighlightSearchResult()
Dim strTest As String
Dim strLen As Integer
strTest = Range("M1")
strLen = Len(strTest)
For Each cell In Range("E1:H100")
If InStr(cell, strTest) > 0 Then
cell.Characters(InStr(cell, strTest), strLen).Font.Color = vbGreen
End If
Next
End Sub

 

[Fluff]

How about

Sub HighlightSearchResult()
Dim strTest As String
Dim strLen As Integer, x As Long
strTest = Range("M1")
strLen = Len(strTest)
For Each cell In Range("E1:H100")
   x = InStr(1, cell, strTest, vbTextCompare)
   If x > 0 Then cell.Characters(x, strLen).Font.Color = vbGreen
End If
Next
End Sub

 

[ferrigeu]

compile error: "End If without block If" - never seen that before...

 

[Fluff]

Oops, delete the "End If" line, I forgot to take it out.

 

[alansidman]

You can use the "Option Text Compare" to avoid case sensitivity

Prevent VBA Case Sensitive - Option Compare Text - Automate Excel

While working in VBA, you often need to compare strings. By default, VBA is case sensitive, so if you compare two same texts and one of them is in upper case and other in lower case, VBA will consider them as different texts. In this tutorial, you will see how to prevent VBA from being…

www.automateexcel.com

 

[ferrigeu]

i used the "FLUFF" method...
also - how can i run the vba code automatically as soon as i change the content of cell $M$1 (i.e. as soon as i hit enter?)

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim strLen As Long, x As Long
   Dim Cl As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "M1" Then
      strLen = Len(Target.Value)
      For Each Cl In Range("E1:H100")
         x = InStr(1, Cl, Target.Value, vbTextCompare)
         If x > 0 Then Cl.Characters(x, strLen).Font.Color = vbGreen
      Next
   End If
End Sub

This needs to go in the relevant sheet module