Forum Discussion

MSeibt16's avatar
MSeibt16
Copper Contributor
Jun 28, 2023

Counting 4 different conditionally formatted colors by column using VBA

Hi All,

 

I am having an issue to where I can not get the count of columns or rows that have been conditionally formatted. I am trying to get the count of each cells colors, not the text that is in each cell. I have tried multiple VBA options, but to avail no luck. I would like to only use the color placed by the conditional format placed on them. 

 

I would attach the file but I am unable to, let me know if I am able to do this. 

 

Please let me know if you need more information or if anyone has any resources. 

 

Thank you for the help.  

excel
Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 29, 2023

    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!

Resources