Assign cell color function

Question

trying to create a function that colour a cells interior (using RGB) according to user input and coded below, can't understand why this is not working though, can anyone advise,

Function RGBC(r, g, b)

Dim src As Range

Set src = Application.ThisCell

With src
.Interior.Color = RGB(r, g, b)
End With
 
End Function

A function called from the worksheet cannot change the format of a cell including the one that calls it. — Scott Craner, Jan 12 '21 at 16:42
now there are workarounds that are tricky to implement and maintain: https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet — Scott Craner, Jan 12 '21 at 16:44

Tragamor · Answer 1 · 2021-01-12T18:40:21.913

A possible workaround to the inability to call directly is to load relevant data into a global variable/type then use a caller function (in this case SheetCalculate) to fire the event you want. For eg.

ThisWorkbook Code

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call ColorRGB
End Sub

Module Code

Private Type RangeRGB
    Update As Boolean
    WS As String
    RNG As String
    RGBColor As Long
End Type

Private tRGB As RangeRGB

Public Function RGBColor(R As Long, G As Long, B As Long)
    On Error GoTo ExitFunction
    tRGB.RNG = Application.Caller.Address
    tRGB.WS = Application.Caller.Worksheet.Name
    tRGB.RGBColor = RGB(R, G, B)
    tRGB.Update = True
ExitFunction:
End Function

Function ColorRGB()
    On Error GoTo ExitFunction
    If tRGB.Update = True Then
        With Worksheets(tRGB.WS)
            Range(tRGB.RNG).Interior.Color = tRGB.RGBColor
        End With
        tRGB.WS = ""
        tRGB.RNG = ""
        tRGB.Update = False
    End If
ExitFunction:
End Function

Please note that if you delete the function from the sheet though, no update is performed

Assign cell color function

1 Answers1