searching in comments within a range

[Eddie69]

Hello all,

I am in need of your assistance. I have a specific range(E14:BO744). Some cells may contain comments. If a cell has a comment I want to check if inside this comment the word "home" is present. If so I want it to be colored. I am searching the internet including this forum. I didn't find a solution to my issue (perhaps I didn't do a good job a searching). I have a code but this one is failing misserabely. Can any of you Masters of Excel help me?
(below code is gathered together from different other macro's).

Sub test2()
Dim search As String
Dim scmt As String
Dim cmt As Comment

search = LCase("home")
Set Rng = Range("E14:BO744")
For Each cell In Rng
 
    If Not cell.Comment Is Nothing Then
       scmt = cmt.Text
        If InStr(scmt, search) <> 0 Then
            ActiveCell.Interior.Color = RGB(112, 48, 160)
            Else
            'do nothing
        End If
        ActiveCell.Interior.Color = RGB(217, 217, 217)
    End If
    
    Next
End Sub

 

[Osvaldo Palmeiro]

Hello Eddie.
See if this is what you're looking for.

Sub test3()
 Dim cell As Range
  For Each cell In Range("E14:BO744")
   If Not cell.Comment Is Nothing Then
    If InStr(cell.Comment.Text, "home") > 0 Then
     cell.Interior.Color = RGB(112, 48, 160)
    Else
     cell.Interior.Color = RGB(217, 217, 217)
    End If
   End If
  Next
End Sub

 

[Eddie69]

Thanks for the quick respons Osvaldo. It looks great but somehow nothing happens. No colors appear (no error messages too)

 

[Osvaldo Palmeiro]

It works for me. Could you upload a sample of your spreadsheet?

 

[Eddie69]

Osvaldo,

I know why it didn't work on my sheet.... Stupid of my. I use conditional format...
I feel so stupid

 

[Osvaldo Palmeiro]

Ok, Eddie.
In addition, maybe you could replace the code line (see below) in order to catch others forms of writting that searched word, like home, Home, HOME

If InStr(LCase(cell.Comment.Text), "home") > 0 Then

 

[Eddie69]

That is a great addition! No I have to figure it out to bypass the conditional format