Cell Color Counts - Automatic Update


Hi All,

This is part 2 of the problem Variatus helped solved so far - thank you my friend. The attached spreadsheet works now with updting the count in cells E20, E21, F20, F21, G20, G21, I20, I21, J20, J21, K20 and K21 based on the colored cells (Blue and Purple). The update entitles the colored cells above row 19 to add a mulitiple of 13 to the sum in cells E20, E21, F20, F21, G20, G21, I20, I21, J20, J21, K20 and K21. But, this does not happen automatically, "Calculate Now" have to be hit/selected first in order to update where there has been a change in color.

Is there someone that knows how this can be automated, so that when I change a color of a cell; E20, E21, F20, F21, G20, G21, I20, I21, J20, J21, K20 and K21 will update with the automatically? The yellow cells is not really important and do not require to be counted really, but there might be an addition of 10-20 more power boards to this one sheet - automatic will help so much with data input!





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.


Answer the Question

You must create an account to use the forum. Create an Account or Login