Passing the value and color of the text in a cell to a user defined function

Question

Currently I am experiencing issues with passing the value of a cell and its respective text color to my user defined function. I am passing the references as ranges and using .Font.ColorIndex. This is then used in a IF statement to determine if any are red (value of 3) and then application.caller.fontIndex=3 to turn the cell text red.

 Public Function Example(AA As Range, BB As Range) As double
 Dim AAcolor, BBcolor As Integer
 AAcolor=AA.font.colorindex
 BBcolor=BB.font.colorindex
 IF BBcolor=3 Or AAcolor=3 Then
  Application.caller.font.colorIndex=3
 End If 

The rest of the code is simply formulas that calculate a double from the ranges inputted which is returned as a double.

To clarify, I am trying to determine the color of the text of the referenced input cells. I am not limited to a UDF to do this if I can call the sub from my UDF.

Answer 1

proof of concept ...

try this ... use F5 or F8 to step through "sub abc123" and watch K5 on worksheet ( I5 is just to supply a variable to the UDF )

it is from a recorded macro, so it is kind of convoluted

note: use another UDF in another cell if you want to do more stuff

Sub abc123()

    ' run on empty worksheet

    Rows("5:5").Delete

    Range("K5").FormulaR1C1 = "56.7"                                         ' just some random data
    Range("K5").FormatConditions.Add Type:=xlExpression, Formula1:="=J5=3"   ' conditional format dependent on value of J5
    Range("K5").FormatConditions(Range("K5").FormatConditions.Count).SetFirstPriority
    Range("K5").FormatConditions(1).Font.Color = -16776961
    Range("K5").FormatConditions(1).Font.TintAndShade = 0
    Range("K5").FormatConditions(1).StopIfTrue = False

    Range("J5").FormulaR1C1 = "=example(RC[-1])" ' UDF returns value to J5
    Stop
    Range("I5").FormulaR1C1 = "2"                ' just some values passed to UDF
    Stop
    Range("I5").FormulaR1C1 = "3"                ' this one should make K5 go red
    Stop
    Range("I5").FormulaR1C1 = "4"

End Sub


Public Function Example(a As Variant) As Variant     ' UDF
    Example = a                                      ' just echo back the value that was received
End Function