A while ago I made a simple macro that automatically counts sections of the same color in a row in Excel and every so often I see people ask about if there's something that does this so posting here. This is a function that paid knitCompanion, and probably some other applications have but this is a free alternative if you already have Excel, or can get Excel ????‍☠. Maybe can be done in Google sheets or LibreOffice, idk I don't really use them but you're free to try to adapt this there too. Hopefully this is not too confusing and works for people I haven't really stress tested it or anything but it's pretty straightforward code.

• White or RGB(255,255,255) is assumed to be not part of the chart so if you're using white in your chart then use an off white or change this value in the VBA code
• "f5:lp178" is the range I'm using in this example as where the code runs, where the color chart is in. You can of course replace this with the range you have the chart. The chart does not have to fill the range or be a rectangle and there can be multiple separate charts within this range. It uses white to see the boundaries of the charts.
• Macro code at the bottom. I think even if you don't use know anything about coding you can read through it and see it's not doing anything like linking outside the file or anything nefarious lol. The isdark() function is just so it can see if an rgb color is dark and so it knows the color should be white. Basically, number() just iterates through the range row by row and if it encounters a different color then it knows a section of color just ended and if the length is >1 (you can change this ofc where it says m.count>1) it will merge those cells and label it with it's length.
• There are online tools to convert images to pixels in excel which could be a starting point
• Running each macro should only take like a couple seconds at the most. Bigger charts take longer

Steps

1. Have Developer tab enabled so you can use macros.
2. Adjust row and column height, color in your chart
3. Click the Developer tab and open Visual Basic
4. Right click at the left where it says VBAproject and click insert ->module and copy paste the below code into the window that opens. Now you can run these macros
5. If you, like me, colored in your chart using conditional formatting, i.e. set it up so if I type in "a" it colors it color a automatically so I can do things with formulas then just the colors and not the values need to be copied to a different sheet since numbering things would replace values in the sheet and affect the formatting. This is what color() does but if you just colored normally without using typing anything then you don't need to do this, skip this step
   1. Name the sheet you have the final design "final_design"
   2. Make a new sheet and adjust the column and row sizes as desired
   3. In this new sheet, click developer tab ->macros-> run color. It will color this sheet based on "final_design", you will then run number in this sheet and not affect "final_design"
6. Within the sheet with the chart you want to number, click developer tab ->macros-> run number
7. save file as an .xlsm

​

Sub color() Set ref_sheet = Worksheets("final_design") Dim c As Range For Each c In ref_sheet.Range("f5:lp178").Cells If c.DisplayFormat.Interior.color <> RGB(255, 255, 255) Then ActiveSheet.Range(c.Address).Interior.color = c.DisplayFormat.Interior.color End If Next c End Sub Sub number() Dim box As Range Dim c As Range Dim m As Range Dim s As Double Dim a As String Dim b As String Set box = Range("f5:lp178") For Each ro In box.Rows a = ro.Cells(1, 1).Address s = ro.Cells(1, 1).DisplayFormat.Interior.color For Each c In ro.Cells If c.DisplayFormat.Interior.color <> s Then b = c.Offset(0, -1).Address If s = RGB(255, 255, 255) Then a = b s = Range(b).DisplayFormat.Interior.color Else Set m = Range(a & ":" & b) If m.Count > 1 Then m.Merge m.HorizontalAlignment = xlCenter Range(a).Value = m.Count If is_dark(m.DisplayFormat.Interior.color) Then m.Font.color = RGB(255, 255, 255) End If End If End If a = c.Address s = c.DisplayFormat.Interior.color End If Next c Next ro End Sub Function is_dark(i As Double) As Boolean Dim r As Double Dim g As Double Dim b As Double r = i Mod 256 g = (i \ 256) Mod 256 b = i \ 65536 is_dark = 0.2126 * r + 0.7152 * g + 0.0722 * b < 128 End Function