You have in Excel cells with different colours. Now the colours are not cosmetics but colours play part when we are counting sums and counts.

First, we need to count how many cells are coloured with certain colours. Like how many cells are having yellow colour ? That is still easy to count visually.

How many different colour can you see ? The issue is that there are several shades of green. What is the frequency of each colour ?

The solution is interior.colorindex property. That is a number which is unique for each colour. We need to have a macro which is checking the colorindex and printing the number to the next cell. Then we can count how many times each number is appearing in the data range.

In Visual Basic editor we have the immediate window. There we can query cells properties like interior.colorindex. We have the cursor in a yellow cell. The colorindex for yellow colour is six.

A macro may check each cell and print the colorindex value.

Sub z_colors()

Dim zc

Do Until ActiveCell.Interior.colorindex = -4142

    zc = ActiveCell.Interior.colorindex

    ActiveCell.Offset(0, 1).Select

    ActiveCell.Value = zc

    ActiveCell.Offset(1, -1).Select

Loop

End Sub

Macro to define colorindex for each cell is relatively simple. The cell without any interior colour has colorindex -4142. The macro is going downwards and storing the colorindex as zc variable. Then macro takes one step right and pastes the number at the right hand side of the cell. The macro takes the next cell in the range. When macro finds the cell with interior colorinxed -4142, then the macro stops there.

The cursor was placed in B3 and the macro was executed. As you see, most of the green cells have 43 as colorindex but one has  50.

Note also that the numbers are not dynamic. If you change the interior colour, you need to execute the macro again.

Copy paste the number and take data – data tools – remove duplicates.

Press ok.

Four unique values remain.

With COUNTIF we can calculate how many times each number exits in the number range.

Still, it might be difficult to map colorindex number and colour.

Sub z_colors2()

Dim zi

Do Until ActiveCell.Value = “”

zi = ActiveCell.Value

ActiveCell.Offset(0, -1).Select

ActiveCell.Interior.colorindex = zi

ActiveCell.Offset(1, 1).Select

Loop

End Sub

This macro fills the cell left from the colorindex number with colour related to colorindex.

Place the cursor in the cell G3 and execute the macro. The macro will colour the cells in the column F.

The data range includes ten cells with yellow interior, two reds, five greens and one turquoise.

How about if coloured cells include values.

We need to count sum based on the interior colours, like what is the sum of the values in red cells.

We can use the same colorindex number, but now we use SUMIF function.

Sum of red cells is six.