Selected Answer
When you change the background colour in a board's range (like E3:G18) on Sheet1 in the attached workbook the colour count in E24:G27 updates automatically. In fact, it updates even if you didn't change any colour, just as you click on another cell. Only one small flaw: It doesn't update if you don't click on another cell. That is Excel's shortcoming, not that of my code. Take pride in having discovered one of the few boundaries of Excel :-) Excel offers no interrupt to capture the change of a cell's colour and I didn't find a work-around.
To be sure to understand: if you change a cell's colour the corresponding number in E24:G27 will continue to be wrong until you click on another cell.
All the code behind Sheet1 in the attached workbook is in the code module belonging to (named after) Sheet1. You can copy this code to other worksheets or workbooks and thereby transfer its functionality.
At the top of the code are instructions for its setup and maintenance. It will be useful for you to understand all of that. Please feel free to ask questions. There are many things which you might change, including adding more colours to track or changing them. I draw your particular attention to the line of code in the Worksheet_Activate event procedure.
BoardAddress = Split("E2,I2", ",")
This is where you can expand the current functionality. The cell addresses E2 and I2 mark the TopLeft cells of each of the two current boards. They both have the same construction, including the rows 24:27 (which you can move elsewhere by changing the Enum Nbd). So, if you were to add another board, say at O2, all you need to do is to modify the code as BoardAddress = Split("E2,I2,O2", ","), and the code will start writing counts into O24:Q27 as if the board were actually there. Be sure to type a comma between cell addresses. Remove the comma when you remove an address.
Edit: =================
The second workbook attached follows a completely different idea. It does everything you have been hoping for (no extra clicks) and it doesn't need code. Instead, it works with conditional formatting. It colours a cell yellow if its value is 1, 2=Blue and 3=Purple. In the attached workbook I left the first board blank. Enter the numbers 1 to 3 to colour the cells.
When you enter 0 (or any number other than 1 to 3) in cell E3 you see 1A-1R. This is the trick! When you give a fixed value to a cell as its format it will display that value regardless of what you enter. So, E3 may have the underlying value of 1 which would cause the CF to colour it yellow and still display 1A-1R to identify the circuit. Cells E24:G27 contain simple COUNTIF functions which update instantly as you change a cell's value.
So the problem shifts to how to give a different cell format to each of the 48 cells in a board grid. I have written code for that and included it in the attached workbook although the workbook doesn't need it (you do lol:). You find it in the module TeachExcel. Place the cursor anywhere within the code and press F5. But be careful! There are no safeguards. You must select the first cell in a board grid - E3, I3 etc. and the number formats will be set for that grid. Select the wrong starting point and 48 cells will be formatted wrongly (close without saving and re-load).
The formulas in E24:E27 are designed for copying to any other column.
