Counting 4 different conditionally formatted colors by column using VBA

MSeibt16 

Here's an example VBA code that counts the occurrences of four different conditionally formatted colors in each column:

Sub CountConditionallyFormattedColors()
    Dim ws As Worksheet
    Dim rangeToCheck As Range
    Dim cell As Range
    Dim colorCount As Variant
    Dim colorIndex As Integer
    
    ' Set the worksheet and range to check
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
    Set rangeToCheck = ws.Range("A1:D10") ' Replace with your actual range
    
    ' Initialize the color count array
    ReDim colorCount(1 To rangeToCheck.Columns.Count, 1 To 4) As Long
    
    ' Loop through each cell in the range
    For Each cell In rangeToCheck
        ' Get the cell color index
        colorIndex = cell.Interior.ColorIndex
        
        ' Increment the corresponding color count based on the color index
        Select Case colorIndex
            Case 3 ' Change the color index values based on your specific conditionally formatted colors
                colorCount(cell.Column, 1) = colorCount(cell.Column, 1) + 1 ' Color 1 count
            Case 4
                colorCount(cell.Column, 2) = colorCount(cell.Column, 2) + 1 ' Color 2 count
            Case 5
                colorCount(cell.Column, 3) = colorCount(cell.Column, 3) + 1 ' Color 3 count
            Case 6
                colorCount(cell.Column, 4) = colorCount(cell.Column, 4) + 1 ' Color 4 count
        End Select
    Next cell
    
    ' Output the color counts
    For i = 1 To rangeToCheck.Columns.Count
        For j = 1 To 4
            Debug.Print "Column " & i & ", Color " & j & ": " & colorCount(i, j)
        Next j
    Next i
End Sub

Please note that this code counts the colors based on the color index, not the RGB color values. If your conditionally formatted colors use RGB values, you will need to modify the code accordingly. 

The text was created with the help of AI.

I hope this helps!