In Excel VBA assigning Fill color for a cell produces #Value

Question

I'm trying to assign a fill color of the active cell based on the integer values in 3 other cells. This is my function:

Function AssignColor(r As Integer, g As Integer, b As Integer)
    ActiveCell.Interior.color = RGB(r, g, b)
    AssignColor = "#" & Application.WorksheetFunction.Dec2Hex(RGB(r, g, b))
End Function

When I use the function, the active cell gets a "#Value" result and the fill color is not set. It appears that any attempt to assign the interior color for a cell produces this error.

Any ideas why this does not assign the fill color. Thanks for your help.

A VBA `UDF` (user defined function), a function which is used in a worksheet cell, can't change cell properties. It only can return a value. So this approach is dead end. — Axel Richter, Apr 03 '16 at 16:49
Among other things see [UDF returns the same value everywhere](http://stackoverflow.com/questions/35910683/udf-returns-the-same-value-everywhere/35956748#35956748) for reasons not to use the [ActiveCell property](https://msdn.microsoft.com/en-us/library/office/ff193314.aspx) in a UDF. — , Apr 03 '16 at 16:55

score 0 · Accepted Answer · answered Apr 03 '16 at 21:59

As said in the comments, a UDF is a function that has to return a valid cell value. Returning an object or doing any object manipulation will result in an error.

You could easily fix your problem by splitting the two tasks: First, have a UDF as you had before, just remove the fill part. Instead, use public variables to store the local ones passed to the function in:

Public r As Integer
Public g As Integer
Public b As Integer

Public Function AssignColor(r1 As Integer, g1 As Integer, b1 As Integer) As String
    r = r1
    g = g1
    b = b1
    AssignColor = "#" & Application.WorksheetFunction.Dec2Hex(RGB(r, g, b))
End Function

Next, in the worksheet module, hook into the worksheet change event and use the public variables to do the color manipulation after the UDF has been fired. This way you avoid the limitation. Remember that the coloring happens in response to someone having entered that UDF into the cell, not as part of calculating the formula result.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Only change cells holding our formula and avoid case sensitiveness.
    If LCase(Left(Target.formula, 12)) = "=assigncolor" Then
        Target.Interior.Color = RGB(r, g, b)
    End If
End Sub

Hope this helps.

In Excel VBA assigning Fill color for a cell produces #Value

1 Answers1