Increment cell value according to cell colour

Closed
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
Application.Volatile
For Each c In rng
If c.Interior.ColorIndex = colour.Interior.ColorIndex Then
Countcolour = Countcolour + 1
End If
Next
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!
0
Thank you ac3mark, but this doesn't answer my question, please treat me as a complete excel dummy. :)
0
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!)

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