Conditional formatting if there contains user comments

[rusa31]

Hello,
I need help.
Output : Range of cell change color when cell contains specific Value and comments

the code below not working

Dim cell As Range
    
    ' Set the range where you want to apply the conditional formatting
    Set rng = Range("A1:A10")  ' Change this range as needed
    
    For Each cell In rng
        If cell.Value = "SpecificValue" And cell.Comment IsNot Nothing Then
            cell.Interior.Color = RGB(255, 0, 0) ' Change color as needed
        End If
    Next cell
End Sub

Thank you.

 

[Bosquedeguate]

Try this code ...

Public Sub FlagValues()
Dim cell As Range
    
  ' Set the range where you want to apply the conditional formatting
  Set rng = Range("A1:A10")  ' Change this range as needed
  
  For Each cell In rng
    With cell(1, 1)
      If .Value = "SpecificValue" And Not (.Comment Is Nothing) Then
        .Interior.Color = vbRed ' Change color as needed
      Else
        .Interior.Pattern = xlNone
        .Interior.TintAndShade = 0
        .Interior.PatternTintAndShade = 0
      End If
    End With
  Next cell
End Sub

test20230822.xlsm
	A
1	1
2	2
3	SpecificValue
4	4
5	5
6	6
7	7
8	SpecificValue
9	8
10	9
Sheet2

 

[Joe4]

Try this code ...

Public Sub FlagValues()
Dim cell As Range
   
  ' Set the range where you want to apply the conditional formatting
  Set rng = Range("A1:A10")  ' Change this range as needed
 
  For Each cell In rng
    With cell(1, 1)
      If .Value = "SpecificValue" And Not (.Comment Is Nothing) Then
        .Interior.Color = vbRed ' Change color as needed
      Else
        .Interior.Pattern = xlNone
        .Interior.TintAndShade = 0
        .Interior.PatternTintAndShade = 0
      End If
    End With
  Next cell
End Sub

test20230822.xlsm
	A
1	1
2	2
3	SpecificValue
4	4
5	5
6	6
7	7
8	SpecificValue
9	8
10	9
Sheet2

The only caveat with that is unlike Conditional Formatting, it is not dynamic. You would have to run it whenever there is a data change.

 

[Bosquedeguate]

Per Joe4's comment above. If you want to use a Conditional Formatting formula do the following
- Create a custom formula like the following

Public Function HasComment(rng As Range)
    HasComment = Not (rng(1, 1).Comment Is Nothing)
End Function

- in the conditional formatting setup - create a new rule for the desired range and enter the following formula for the selected cell
=AND(A1="SpecificValue",HasComment(A1))

Note: A1 was my selected cell and the range was (A1:A10)