Count By Colour Of The Sheet Tab

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
  ActiveSheet.Range("B2:B4").ClearContents
  
'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