Count worksheets base on its colour

thedogg · Apr 4th 2016

I'd like to count how many of my sheets in workbook has tab in green. I need this check just for visible sheets.
For example:

I have 5 sheets +1 hiden, two sheets are green and two white. I would like to display how may tabs is green and how many is white except hiden sheet.

John_w · Apr 4th 2016

Re: Count worksheets base on its colour

Try this.

Code

Public Const rgbWhite As Long = 16777215    'RGB(255, 255, 255)
Public Const rgbGreen As Long = 5287936     'RGB(0, 176, 80)




Public Sub Count_Sheet_Tab_Colours()


    Dim ws As Worksheet
    Dim numWhite As Integer, numGreen As Integer
    
    numWhite = 0
    numGreen = 0


    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible Then
            numWhite = numWhite + -(ws.Tab.Color = rgbWhite)
            numGreen = numGreen + -(ws.Tab.Color = rgbGreen)
        End If
    Next
    
    MsgBox "White: " & numWhite & vbCrLf & "Green: " & numGreen
    
End Sub

Display More

thedogg · Apr 6th 2016

Re: Count worksheets base on its colour

Thanks, it works great!!!

Count worksheets base on its colour

Participate now!

Share