VBA Conditional formatting on cells with comments

[jsflbot]

Hello!

I'm trying to figure out how to conditional format cells with particular text in the comment section. If the comments say "Ad" then I would like the box to turn red and if the comments say "TPR" then I want it to turn blue, and if the comments say "Both", then it would turn green.

I used this VBA Code, but it's turning every cell blue if it has a comment....help please
Sub HighlightCommentCells()
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue
On Error GoTo 0
End Sub

 

[Joe4]

You can incorporate the UCASE function, which converts all text to Upper case in your VBA code, to handle that.

Sub MyCommentMacro()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
    For Each cell In Range("AC5:AN1000")
        If Not cell.Comment Is Nothing Then
            Select Case UCase(cell.Comment.Text)
                Case "AD"
                    cell.Interior.Color = vbRed
                Case "TPR"
                    cell.Interior.Color = vbBlue
                Case "BOTH"
                    cell.Interior.Color = vbGreen
            End Select
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

Test it out and see (that is the best way of finding our if it works or not).