cell value from a colour

[j4ymf]

hello all

at work they like to use colours to indicate a value

red = 3
green = 2
yellow = 1

is there a formula that if the cell is red = 3

so what I'm after is,
if A1 is red in colour in B1 the cell = 3
formula = if red= 3, green = 2, yellow = 1


please help if you get what I'm on about
thank you

 

[lrobbo314]

You can use a UDF.

Function ColorVal(r As Range) As Variant
With r
    Select Case .Interior.ColorIndex
        Case 3
            ColorVal = 3
        Case 4
            ColorVal = 2
        Case 6
            ColorVal = 1
        Case Else
            ColorVal = "N/A"
    End Select
End With
End Function

 

[j4ymf]

hello irobbo314, thank you for you reply

How do I use this formula
or how do I call back the answers

if Im using A1 to A10 with colour cells how do I find the answer

B1 = ColorVal total????

 

[lrobbo314]

Follow the directions in my signature to paste the code. Then use it like a normal formula. In the worksheet type =ColorVal(B1)