Increment cell value according to cell colour

SecretS - Jan 16, 2017 at 10:19 AM
 SecretS - Jan 18, 2017 at 03:36 AM
Hi Guys,

Can anyone help with a problem I have. I need to increase a cell count by 1 or 0.5 based on the fill colour of the cell. I am using the following code to increment the count by 1, which works fine, but i also need to increment the cell count by 0.5 if another cell is a different colour. I am using Excel 2010. Any help would be much appreciated.

Function Countcolour(rng As Range, colour As Range) As Long
Dim c As Range
For Each c In rng
If c.Interior.ColorIndex = colour.Interior.ColorIndex Then
Countcolour = Countcolour + 1
End If
End Function

Many Thanks

4 responses

Blocked Profile
Jan 16, 2017 at 02:11 PM
Do not use a color to determine the displayed result, but use the value. The only "resource" that cares about the color is a HUMAN! What value in the cell determines the color, and check for the internal value of the cell, not the color!
Thank you ac3mark, but this doesn't answer my question, please treat me as a complete excel dummy. :)
Blocked Profile
Jan 17, 2017 at 04:55 PM
No problems!

What determines the colors? A number? Check the value of the cell, not the color.

So where you use: c.Interior.ColorIndex, change that to validate a value, instead of some color index. Something like:
if c.value =.05 then Countcolour = Countcolour + 1
if c.value =1 then Countcolour = Countcolour + .05

Initialize variables that are equal to the color name, if you have to use the color index(in that case, this sounds like homework, at which I say I don't do homework!)

Thank you very much ac3mark, your help is very much appreciated. :)