QP School
Count colored cells with VBA - Printable Version

+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Count colored cells with VBA (/showthread.php?tid=5265)



Count colored cells with VBA - Qomplainerz - 07-27-2023

Example:

Sub CountColoredCells()
    Dim countColor As Long
    Dim cell As Range
    Dim targetColor As Long

    'Specify the target color (RGB value) to count
    targetColor = RGB(255, 0, 0) 'Red color in this example

    countColor = 0 'Initialize the counter

    'Loop through each cell in the range
    For Each cell In Range("A1:A10")
        'Check if the cell's background color matches the target color
        If cell.Interior.Color = targetColor Then
            countColor = countColor + 1 'Increment the counter
        End If
    Next cell

    MsgBox "Number of cells with the target color: " & countColor
End Sub

Explanation:

The code defines a subroutine named CountColoredCells.
It declares variables for the counter (countColor), a loop variable (cell), and the target color (targetColor) you want to count. 
In this example, we use the RGB value for the color red (255, 0, 0).
The counter countColor is initialized to 0.
The For Each loop is used to loop through each cell in the range "A1:A10".
The If statement checks if the background color of the current cell matches the target color (targetColor = RGB(255, 0, 0)). 
If there is a match, the counter countColor is incremented by 1.
Finally, a message box displays the number of cells with the target color.