I'm using Excel 2013 and I want to write a Function in VBA that has two Parameters (Sourcecell and Destinationcell) and simply copies the Backgroundcolor from Sourcecell to the Destinationcell. This is what I have:
Function setRGB2(ByVal sCell As Range, ByVal dCell As Range)
Dim lngColor As Long
Dim B As Long
Dim G As Long
Dim R As Long
On Error GoTo Fehler
lngColor = sCell.Interior.Color
B = lngColor / 65536
G = (lngColor - B * 65536) / 256
R = lngColor - B * 65536 - G * 256
Range(dCell).Interior.Color = RGB(R, G, B)
'Range(dCell).DisplayFormat.Interior.Color = RGB(R, G, B)
Fehler:
With Err
End With
End Function
I get the Error:
improper use of a property
For Example my Sourcecell is B16 and my Destinationcell is B46. So in B46 I write =setRGB2($B$16;B46). I tried setting the Color directly like dCell.Interior.Color = sCell.Interior.Color but that didn't work.
EDIT
I have added the Declaration for the Parameters. But it seems to be another Problem. Even if I do dCell.Interior.ColorIndex = 1 it throws the same error.
User Defined Functions cannot change the state of worksheet/cells. In other words, cannot change colors. (Source)
But Subs can do, so you can design a Function and then call the function from the Sub.
But in your case, a Sub with parameters should do and you can call it in your VBA code whenever you want, in an easy way.
Sub testing()
setRGB2 [A1], [A2]
End Sub
Private Sub setRGB2(ByRef sCell As Range, ByRef dCell As Range)
dCell.Interior.Color = sCell.Interior.Color
End Sub
Also, I said at start of my answer that an UDF cannot change the state of a worksheet, but if for any reason you really need it, there is a way to do it in a really complex and hardcore way.
Also, in your question you say:
For Example my Sourcecell is B16 and my Destinationcell is B46. So in B46 I write =setRGB2($B$16;B46)
This is wrong because you are creating a circular reference, and that's causing you an error.