Fill Color based on Comment Text
 

Is there a way to change the fill color of all cells in the active sheet
which have comments, based on a specific text string in those comments?

Crude example:

For Each Cell in ActiveSheet Where comment.text ="Test"
Cell.Interior.ColorIndex = xlNone

Thanx.
- Mike

Fill Color based on Comment Text
 

Hi Mike,

Try:

Sub aTester01()
Dim rng As Range
Dim rCell As Range
Const sStr As String = "Hi" '<<== Your search string

Set rng = Range("A1:D100") '<<===== CHANGE

On Error Resume Next 'In case no comments found1
Set rng = rng.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
If InStr(1, rCell.Comment.Text, sStr, vbTextCompare) Then
rCell.Interior.ColorIndex = 6
Else
rCell.Interior.ColorIndex = xlNone
End If
Next rCell
End If
End Sub
'<<====================

---
Regards,
Norman



"MikeF" wrote in message
...

Is there a way to change the fill color of all cells in the active sheet
which have comments, based on a specific text string in those comments?

Crude example:

For Each Cell in ActiveSheet Where comment.text ="Test"
Cell.Interior.ColorIndex = xlNone

Thanx.
- Mike

Fill Color based on Comment Text
 

Thank you!



"Norman Jones" wrote:

Hi Mike,

Try:

Sub aTester01()
Dim rng As Range
Dim rCell As Range
Const sStr As String = "Hi" '<<== Your search string

Set rng = Range("A1:D100") '<<===== CHANGE

On Error Resume Next 'In case no comments found1
Set rng = rng.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
If InStr(1, rCell.Comment.Text, sStr, vbTextCompare) Then
rCell.Interior.ColorIndex = 6
Else
rCell.Interior.ColorIndex = xlNone
End If
Next rCell
End If
End Sub
'<<====================

---
Regards,
Norman



"MikeF" wrote in message
...

Is there a way to change the fill color of all cells in the active sheet
which have comments, based on a specific text string in those comments?

Crude example:

For Each Cell in ActiveSheet Where comment.text ="Test"
Cell.Interior.ColorIndex = xlNone

Thanx.
- Mike