# Cell Color Counts - Automatic

0

People,

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.

Woutie

0

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

Application.Volatile
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(\$E\$3:\$G\$18,\$E\$26)
=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.

### Discussion

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?

Regards
Woutie
woutie (rep: 8) Jul 29, '18 at 7:33 am
Hello Woutie,