• 0

Count By Colour Of The Sheet Tab

  • 0

Hi guys, me again.
Was wondering if it was possible to count by the colour of a sheet tab? I categorise files depending on their status by colour – red for incomplete, orange for in progress and green for done. I want to be able to count how many are red so I know how many need to be looked at, and so on and so forth.


1 Answer

  1. This code should count your tabs by color without having to tell it what colors are in your workbook.

    As written it assumes 3 colors, but it could be modified to count as many diffferent colors as you have.

    Option Explicit
    Sub TabClrCntDD03()
    Dim ws As Worksheet
    Dim clrIdx, nxtRw, thisIdx As Integer
    'reset counters and colors in A2:B4
      ActiveSheet.Range("A2:A4").Interior.ColorIndex = -4142
    'initialize row counter
       nxtRw = 2
    'loop through ColorIndex Numbers and Worksheets
       For clrIdx = 1 To 56
        For Each ws In ActiveWorkbook.Worksheets()
    'increment color counter when Tab Color matches ColorIndex
         If ws.Tab.ColorIndex = clrIdx Then _
           thisIdx = thisIdx + 1
        Next ws
    'If a sheet tab matched a ColorIndex Value then
    'Color cell in Column A and put counter in Column B
         If thisIdx > 0 Then
          Range("A" & nxtRw).Interior.ColorIndex = clrIdx
          Range("B" & nxtRw) = thisIdx
     'increment Row counter and reset ColorIndex counter
           nxtRw = nxtRw + 1
           thisIdx = 0
         End If
       Next clrIdx
    End Sub

    • 0