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 access text of a Comment like this:

Range("P1").Comment.Text

So you could just loop through the range you want to look, and replace the hard-coded range reference with the dynamic cell reference.

 

[jsflbot]

You can access text of a Comment like this:

Range("P1").Comment.Text

So you could just loop through the range you want to look, and replace the hard-coded range reference with the dynamic cell reference.

Joe, I have no idea how to do this since I dont even know VBA coding. I got the code from a page i found online. Any way you can guide me through this?

 

[Joe4]

Can you explain which cells you want to check?
Are they in a particular range?
You don't have any comments on empty cells, do you?

 

[jsflbot]

Can you explain which cells you want to check?
Are they in a particular range?
You don't have any comments on empty cells, do you?

yes they are, in a large range AC5:AN1000. no comments on empty cells for now. but once a cell has a number ($) included then a comment will follow

 

[Joe4]

Will it always go down to row 1000, or did you just pick some large number you don't ever expect to exceed?
The reason I ask is because looping can be inefficient, and if we can limit the number of cells we have to hit, the better the performance our macro will have.

If there some column we can use to determine where the data ends? Or to put it another way, if there is data in a row, is there any column within the row that MUST be populated.
For example, maybe every row with data MUST have a value in column AC. Is there any column like that?

 

[Joe4]

Here is code that will do what you want. As I mentioned in the previous post, we may be able to make it more efficient if we can hone in on the actual last row.

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 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

 

[jsflbot]

Here is code that will do what you want. As I mentioned in the previous post, we may be able to make it more efficient if we can hone in on the actual last row.

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 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

Thanks Joe! it worked like a charm. Now, if users enter their comments will this macro run automatically or do I manually have to run it after users enter their data? Is their a way to have it run automatically, sort of like how conditional formatting works?

 

[Joe4]

There are VBA event procedures which run automatically, but none, per se, that run on a change in a Comment.
There is one that run on the change of a cell value, and one that runs on the change in the selected cell.
Chip Pearson did a good write up on them some years ago: Events In Excel VBA

 

[jsflbot]

There are VBA event procedures which run automatically, but none, per se, that run on a change in a Comment.
There is one that run on the change of a cell value, and one that runs on the change in the selected cell.
Chip Pearson did a good write up on them some years ago: Events In Excel VBA

I'll definitely read that article. One last question, does it matter how the actual word is entered into the comment section? For example, will the code run only if its typed up as "Ad" or can it be "ad" as well?