Assigning numerical values based on font colors

[gary2068]

In excel 2013, is it possible to automatically assign a numerical value in a given cell, let's say cell X10, based on the font color of a different cell, lets say V12? The font color can change in cell V12 up to 10 different colors based on data entries from a seperate location on the spreadsheet.

For example, if the font in V12 = blue then 1 in X10, if the font color in V12 = red then 2 in X10....and so on?

Also, the value in cell V12 is generated thru a macro (if that makes any difference)

Thanks in advance for your help.

 

[Joe4]

Welcome to the Board!

Excel formulas can only run off of values in cells, not on the formatting (like color) of cells.
So to do what you want would require some sort of VBA.

Are you open to a VBA solution?
We can either create a Macro (procedure), or a User Defined Function in VBA (our own custom function that you would use like other functions).

 

[gary2068]

Sure....VBA is fine. Pls keep in mind that the numerical value needs to be assigned to the font color and not the cell color. thanks

 

[Joe4]

OK, insert a new VBA module into your current workbook, and paste this code in it:

Function ColorCheck(cell As Range) As Long
'   Check the color of a cell and return a value depending on what it is

    Dim colorCode As Long
    
'   See how many cells in range
    If cell.Count > 1 Then
'       If more than one cell, set value to 0
        ColorCheck = 0
        Exit Function
    Else
'       Otherwise, capture the code of the current cell
        colorCode = cell.Font.Color
    End If
    
'   Determine which values to bring back
    Select Case colorCode
        Case 12611584 'blue font color
            ColorCheck = 1
        Case 255 'red font color
            ColorCheck = 2
        Case 0 'black font color
            ColorCheck = 3
        Case Else 'any other color
            ColorCheck = 99
    End Select

End Function

Note this particular section here:

    Select Case colorCode
        Case 12611584 'blue font color
            ColorCheck = 1
        Case 255 'red font color
            ColorCheck = 2
        Case 0 'black font color
            ColorCheck = 3
        Case Else 'any other color
            ColorCheck = 99
    End Select

This is where you have each color code, and the associated value you want to return with it (i.e. if color is red, which is code 255, return a value of 2).
You will need to update this section for all your values. Note that you can insert more cases, just do it before the "Case Else", which should always be your last "Case" statement (i.e. this is what to do if NONE of the conditions above are met).

If you are unsure what the color codes are, here is an easy way to get them.
Change the font of cell A1 to the color you want to get the code for, then run this VBA code (you can paste it in the same VBA module as your function).

Sub GetColor()
    MsgBox Range("A1").Font.Color
End Sub

Once you have the function completely written, you would just use it like any other function in Excel.
So if you wanted cell X10 to return a code based on the font color in cell V12, then you would just enter this formula in cell X10:

=ColorCheck(V12)

 

[gary2068]

Excellent Joe4.....much appreciated!

 

[Joe4]

You are welcome.
Glad I was able to help!