excel - 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

Answer 1

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