Automatically run a macro when I manually change a cell colour

[FuzzyOwl]

Hi,

I'm using a spreadsheet to track the cases I'm working on at work. I've got a column (G) in which I colour code my cases by urgency. Red is immediate action, orange is urgent action etc. I change these manually when something comes in that warrants a change in a case's urgency (i.e. a screaming email from my boss or the client).

I found a neat little macro online, which has the name "CountCcolor" which looks like this:

Function CountCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function

Essentially it allows me to keep track of how many cases I have of each urgency level, which is nice. Right now I have 3 marked as red, 2 as orange etc. Problem is, it doesn't automatically update the count when I change a cell colour. So if I work on one of those red ones and change it to orange, I now have 2 red and 3 orange, but the count won't update unless I manually make the macro run by clicking in the cells where the formula is.

I'll be honest, I'm not great at this kind of thing, and it's not something I can devote much work time to, so I'm stuck for a solution. Is there any way I can set things up so that this macro runs automatically when I change the colour of a cell?

Thanks in advance

 

[Fluff]

Hi & welcome to MrExcel
Changing the colour of a cell will not trigger anything. One option is to use a code that will cause the sheet to recalculate everytime you select a new cell, but depending on your sheet, this may cause more problems than it's worth.