Cell Color Counts - Automatic



I have the attched spreadsheet which I need to be automatic based on colors I change in it. If I update Change over circuits (Light Blue) in a board it should recognize the color change and automatically reserve that amount of current (13A per circuit) of the capacity (200A) of that particular board. Next step would be when I add/allocate a new circuit by coloring it Purple, its should automatically reserve that amount of current (13A per circuit) of the capacity (200A) of that particular board.

In short, I need Cells E20, E21, F20, F21, G20, G21, I20, I21, J20, J21, K20 and K21 to update automatically as I change the colors e.g. Ligh Blue amd Purple. ellow availbe circuit will be turned to Purple if there is capacity. The aim is to get the boards as fully loaded as possible.

Not sure if this is possible, but if it is, it will save me at least 30 minutes per board schedule.

Thank you in advance,




Selected Answer

I'm afraid I don't understand the required calculations in your cells E20:K21 but I believe that the UDF (User Defined Function) below will facilitate them.

Function ColorCount(Target As Range, _
                    Example As Range) As Long
    ' 29 Jul 2018
    Dim Cell As Range
    Dim Col As Long
    Col = Example.Interior.Color
    For Each Cell In Target
        If Cell.Interior.Color = Col Then
            ColorCount = ColorCount + 1
        End If
    Next Cell
End Function

In a nutshell: the above function counts the number of cells in a range which have the same colour as a sample cell. In cells M8:M9 of the attached workbook there are two examples of how to call the function.

=ColorCount(Board1A, Yellow)

The difference is in that the second example uses named ranges. I named E3:G18 as Board1A and each of the five cells E26:E31 by its colour. Named ranges are included in Excel's Intellisense feature, meaning you type "Bo" and Board1A will be suggested. The resulting formula is also easier to read and a lot easier to maintain because any change will be executed in the Name Manager instead of modifying each formula in your worksheet.

To quickly set a name for a range, select the cell or range and type the desired name in the Name Bar at the top left of the screen. After naming, the Name Bar will show the name of the range when you select it again.

To install the UDF press Alt+F11 to open the VB Editor and create a new standard code module. Its default name will be Module1. Paste the code into this module. Don't use any of the existing modules. As an alternative, open both the attached workbook and your own and drag the code module TeachExcel from the attached workbook's VBA project to your own in the VB Editor's Project Explorer window (that's the panel on the left where you see all the modules listed).

If properly installed, Excel's Intellisense will suggest ColorCount when you type "=Col" and you can select it. Then add the two required parameters and close the bracket. The first is the range in which to count and the second a cell which has the colour you want to count.

The UDF is volatile. That means that it will be updated whenever any part of the sheet is calculated. Unfortunately, changing the colour of a cell doesn't trigger any calculation in the sheet and, therefore, also doesn't update ColorCount. You can force an update by clicking on Calculate Now on the ribbon's Formulas tab.

The UDF can be used as part of larger formulas, just like Excel's built-in functions. In the attached workbook M20 has an example.

=COUNTA(Board1A) - ColorCount(Board1A, Blue)

This formula calculates the number of cells in E3:G18 which are not blue. However, please do let me know if you require help to implement the capabilities of the UDF I have supplied here in the calculations you need in E20:K21. Just explain what needs to be calculated, and how, in a little more detail than you have done so far.



Hi Variatus, 
Thank you so much for your reply and attached sheet! I can work with that and tailor it to my needs, but my biggest problem would still be to have it updated automatically. This would be very important to me for time savings and key strokes (My company track our ergonomics and wants us to type less all the time). Would this be possible at all or should I just except the fact it will not be?

woutie (rep: 8) Jul 29, '18 at 7:33 am
Hello Woutie,
The work flow on this site is (a) ask a question, (b) get an answer, (c) accept the answer and move on. You seem to be holding out for a better answer which is fair enough but impossible in this case. A better answer needs more information, and by the time you provide more information it won't be the same question anymore. I think you will progress faster if you go with the flow. Consider the following my attempt to help you formulate a new question.
Start a new question by showing what you have and what you want to do. The former has changed after you implemented my UDF, and the latter depends upon your workflow about which you have only said "when I add/allocate a new circuit". Not enough. Automation requires precise knowledge of what you click at which point in your process.
My approach would be to argue that your method of working is inefficient and won't be improved by automating it. I would try to invert it. Instead of colouring cell manually and then counting what was coloured, automate the colouring and have no need for counting. Imagine right-clicking on a cell, selecting a colour from a context menu and have that colour applied to the cell and E20:K21 updated at the same time. That's the question you should preare, if it fits into your workflow.
Variatus (rep: 3378) Jul 29, '18 at 9:14 am
Good morning Woutie,
Thanks for awarding points. I thought about your problem overnight and think there is a solution closer to what you may have had in mind originally :-) 
There are two keys. One is the exact formulas in E20:K21. They should now be working the way you want but requiring a nudge. They must be moved from the worksheet to the VBA Project, including the ColorCount function, so that when you change the color (in whatever way) these cells are updated. The other is the number of boards on one worksheet and how you would add another one. Too big for this discussion. I hope you will decide on having another thread.
Variatus (rep: 3378) Jul 29, '18 at 8:54 pm
Add to Discussion

Answer the Question

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