0

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.

Thomas Breakell
  • 61
  • 2
  • 7
  • https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel UDF can only return a value - it cannot update the worksheet directly. – Tim Williams Jul 14 '17 at 20:07
  • workaround : ... assuming that data is in cell C5 .... run a UDF in cell B5 "_=Example(C5)_" , that takes value of C5, and returns a 3 .... do conditional format on C5 to go red if "_=B5 = 3_" – jsotola Jul 14 '17 at 20:34
  • The application.caller.font.colorindex=3 changes the font color of the cell C5. Is it possible to nest a Sub to determine the font color from the given range? – Thomas Breakell Jul 17 '17 at 12:37

1 Answers1

0

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
jsotola
  • 2,238
  • 1
  • 10
  • 22