Highlight Cell With Search Values

[anish.ms]

Hi,

Request help in below code which highlights the search values if in Upper, Lower or Proper case. How do I highlight the search text irrespective of it's case. For example the below code will not highlight cells with value "VBA Express" if I input "vba express" and search Thanks in advance for the help!



Option Explicit


Sub HighlightSearchValues()
Dim Rng As Range
Dim i As Integer
Dim c As Variant
c = InputBox("Enter Value To Highlight")
For Each Rng In ActiveSheet.UsedRange
'If Rng = c Then
If Rng = c Or Rng = UCase(c) Or Rng = LCase(c) Or Rng = WorksheetFunction.Proper(c) Then
Rng.Style = "Note"
i = i + 1
End If
Next Rng
MsgBox "There are total " & i & " " & c & " in this worksheet."
End Sub

[Dave]

This seems to work. Dave

Sub HighlightSearchValues()Dim Rng As Range
Dim i As Integer
Dim c As Variant
'ActiveSheet.UsedRange.Style = "normal"
c = InputBox("Enter Value To Highlight")
For Each Rng In ActiveSheet.UsedRange
If InStr(Rng.Text, CStr(c)) Then
Rng.Style = "Note"
i = i + 1
End If
Next Rng
MsgBox "There are total " & i & " " & c & " in this worksheet."
End Sub

[anish.ms]

Hi Dave
Thanks
I tested and found to be only the specific case is working

[Paul_Hossler]

Option Explicit


Sub HighlightSearchValues()
Dim Rng As Range
Dim i As Integer
Dim c As Variant

c = LCase(InputBox("Enter Value To Highlight"))
For Each Rng In ActiveSheet.UsedRange
If LCase(Rng.Value) = c Then
Rng.Style = "Note"
i = i + 1
End If
Next Rng


MsgBox "There are total " & i & " " & c & " in this worksheet."
End Sub

[anish.ms]

Thanks Paul_Hossler (http://www.vbaexpress.com/forum/member.php?9803-Paul_Hossler) :thumb