How to count cell colours allocated through a macro in excel 2007?

Question

I have created a spreadsheet in excel 2007 for teachers to monitor progress and attainment based upon various parameters.

To create the rules for the parameters I have used a set of macros which are working brilliantly and the cells containing the student scores change colour depending upon whether they are progressing at, below or above the expected level (yellow, red and green- original I know!!).

I am now trying to create a 'drop-in' spreadsheet that will allow staff to copy and paste from the master sheet (which contains all of the students in the Year group) and get feedback on their own class specifically. Everything has worked well until I came to what I thought would be the easiest part- getting excel to count the number of different coloured cells in each column. I know this cannot be done through formula unless you have the xCELLcolor add-in (which we don't) so I wrote a very simple CountColor script using VBA.

I can now apply a formula to count any coloured cell based upon an existing cell colour but I cannot get it to count the coloured cells copied over from the master spreadsheet. I keep getting the answer '0' or '29' which is the total number of cells in the column!

Is this something to do with the way in which the master spreadsheet allocates the cell colour through the macro, does excel not recognise the cell as having a colour when it is copied and pasted in to the 'drop-in' spreadsheet?

I am completely stumped because it works for columns that I format with colour manually! Any suggestions?

I know this cannot be done through formula unless you have the xCELLcolor add-in (which we don't)? Are you sure ;)? See THIS Instead of 24 try it with 63 which returns the fill (background) color of the cell. =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1)) — Siddharth Rout
Thanks @Siddharth Rout I can see how this is supposed to work, unfortunately I cannot get it to work. I have inputed the formula into Name Manager but now I am not sure how to get the response I want. Let's say that I want to count how many red cells there are in the column H2:H30 and return the answer in cell B36. I have an example of the specific red colour in cell A66 that I was using as a reference. What formula would I write in B36? Thanks for your help, as you have no doubt guessed I am learning as I go with excel! — user2319034

steveo40 steveo40 · Accepted Answer · 2013-05-09T20:27:50

I would use VBA for this sort of thing. Type popping this bit of code into a module within your master spreadsheet:

Public Function CountColouredCells(rge As Range, colour As Integer) As Long

Dim rge2 As Range
Dim count As Long

For Each rge2 In rge
    If rge2.Interior.Color = colour Then
        count = count + 1
    End If
Next

CountColouredCells = count

End Function

Then you can use it like any other Excel function in your workbook: =CountColouredCells(A4:D12,255)

Where the second argument is the colour you're interested. 255 is red. You can find out the others by recording a macro, changing a cell colour and then looking at the VBA.

How to count cell colours allocated through a macro in excel 2007?

1 Answers