Increment cell value according to cell colourClosed

Question

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

ac3mark · Answer

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!

SecretS · Answer

Thank you ac3mark, but this doesn't answer my question, please treat me as a complete excel dummy. :)

ac3mark · Answer

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!)

SecretS · Answer

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

Increment cell value according to cell colour

4 responses

Similar discussions

Subscribe To Our Newsletter!