VBA Msgbox count cell specific color

[Dao Ha Quang]

I want to count all the cells with color RGB(255,0,0) on the sheet and display in 1 Msgbox the number of those cells.
If there is no cell with that color, it will notify Msgbox "No cell..."
Thank you

 

[DanteAmor]

Try:

Sub msg_countcolor()
  Dim sh As Worksheet
  Dim f As Range, r As Range
  Dim cell As String
  Dim n As Long
  
  For Each sh In Sheets
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Color = RGB(255, 0, 0)
    Set r = sh.UsedRange
    Set f = r.Find("", , xlValues, xlPart, , , False, , SearchFormat:=True)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        n = n + 1
        Set f = r.Find("", f, xlValues, xlPart, , , False, , SearchFormat:=True)
      Loop Until cell = f.Address
    End If
  Next
  Application.FindFormat.Clear
  If n = 0 Then
    MsgBox "No cell..."
  Else
    MsgBox "Number: " & n
  End If
End Sub

 

[Dao Ha Quang]

Try:

Sub msg_countcolor()
  Dim sh As Worksheet
  Dim f As Range, r As Range
  Dim cell As String
  Dim n As Long
 
  For Each sh In Sheets
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Color = RGB(255, 0, 0)
    Set r = sh.UsedRange
    Set f = r.Find("", , xlValues, xlPart, , , False, , SearchFormat:=True)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        n = n + 1
        Set f = r.Find("", f, xlValues, xlPart, , , False, , SearchFormat:=True)
      Loop Until cell = f.Address
    End If
  Next
  Application.FindFormat.Clear
  If n = 0 Then
    MsgBox "No cell..."
  Else
    MsgBox "Number: " & n
  End If
End Sub

Thank so much