OK, insert a new VBA module into your current workbook, and paste this code in it:
VBA Code:
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:
VBA Code:
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).
VBA Code:
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: