Change Application.Caller Fill Color

Question

I am trying to get my function to alter the cell color of the cell where the function is being called. I found some good advice on this question, but I'm running into a #VALUE! error with the following code (the rest of the function works as expected with this omitted).

Inside of main function:

Dim checkCounter As Long

checkCounter = 0

When certain if statements are executed, checkCounter = checkCounter + 1.

Also inside of main function:

If checkCounter <> 0 Then

    With Application.Caller
        .Parent.Evaluate "Changeit(" & .Address(False, False) & ")"
    End With

'Else (would like to get this working as well)

    With Application.Caller
        .Parent.Evaluate "Clearit(" & .Address(False, False) & ")"
    End With'

End If

ChangeIt Sub:

Sub ChangeIt(cl as Range)
    cl.Interior.ColorIndex = 22
   'cl.Font.ColorIndex = 9 (would like this to function as well)'
End Sub

(Potential) ClearIt Sub:

'Sub ClearIt(cl as Range)
    cl.Interior.ColorIndex = 0
    cl.Font.ColorIndex = 1
End Sub'

If I edit a precedent cell (for function parameters), the fill color will apply but the #VALUE! error still remains. Any way to fix this?

Side question: Why does/should .Parent.Evaluate "Clearit(" & .Address(False, False) & ")" work with Clearit when the sub is ClearIt?

If your main function is returning #VALUE then it would be useful to post the full code for that function (or a minimal function which reproduces the problem). For the last question - VBA is not case-sensitive. — Tim Williams, Nov 11 '21 at 21:05
FYI for the font color - I think that's one thing you can do directly from your UDF. — Tim Williams, Nov 11 '21 at 21:06

Spinner · Answer 1 · 2021-11-12T00:37:49.417

In essence the color-changing elements of your code should work. So I suspect #VALUE isn't due to that. Nonetheless, here is a tested and working example of what you're trying to do.
It's also a more complete (and somewhat alternate) approach to the color-setting elements.
It supports 'highlighting' with a wider range of colors, as well as returning colors to defaults (should the conditions subsequently require that):

''' Note: Change as appropriate to suite your fuller function
Public Function TestColorSetting(Optional Value = 0)

    Dim checkCounter%, lgIntColor&, lgFontColor&
    
''' Set any color you want for interior and font (not limited by the color index pallet)
''' Note: To get a color value, 'Record Macro' yourself setting colors via the 'More Colors...' option
    lgIntColor = 16764006
    lgFontColor = -16763956
    
''' For testing, I've set checkCounter to value passed (or zero by default)
''' Obviously: Your 'other' code will do this.
    checkCounter = Value

''' Run either HighlightColors or DefaultColors (based on checkCounter)
    With Application.Caller
        If checkCounter <> 0 Then
            .Parent.Evaluate "HighlightColors(" & .Address(False, False) & "," & lgIntColor & "," & lgFontColor & ")"
        Else: .Parent.Evaluate "DefaultColors(" & .Address(False, False) & ")"
        End If
    End With

End Function

''' Sub to set highlight colors
Sub HighlightColors(Target As Range, IntColor&, FontColor&)
    With Target
        .Interior.Color = IntColor
        .Font.Color = FontColor
    End With
End Sub

''' Sub to set default colors
Sub DefaultColors(Target As Range)
    With Target
        .Interior.Pattern = xlNone
        .Font.ColorIndex = xlAutomatic
    End With
End Sub

Separately:
Another thing to consider (if you haven't already) is using Conditional Formatting.
That's all purpose-built for this sort of thing, and given the conditions seem relatively simple, might be a better fit?

Change Application.Caller Fill Color

1 Answers1