Set cell color with VBA results in "#VALUE!"
What you're trying to do is not allowed in Excel. You CANNOT change the
format of a cell with a user-defined function (UDF). That is why the msgbox
works (because it's not changing anything about the cell), but the attempt to
change the color bombs. The reason you get #VALUE is that your function
errors out before it returns. The error is probably occuring when you try to
set a cell interior color.
Also, a UDF generally should return a VALUE, e.g.:
Public Function testColor() as Long
test
testColor = 15 ' <-- This value should show up in the cell with
=testColor()
End Function
HTH,
Eric
"Christian Schratter" wrote:
Following code is now in the the module "Module1", which is part of the
VBAProject (colorTestXLS.xlsm) (which is a macro enabled xls file as you can
see):
-- CODE <--
Public Function testColor()
test
End Function
Sub test()
MsgBox ("In SUB")
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i
End Sub
-- RESULT <--
If I call the testColor function from a cell in a worksheet ("=testColor()")
then I actually get the message box pop-up telling me "In SUB" but nothing
else. The value of the corresponding cell changes again to "#VALUE!".
"AltaEgo" wrote:
Try this:
Sub test()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i
End Sub
--
Steve
"Christian Schratter" wrote
in message ...
Hello
I made a function in a new module with a function which looks like this:
Public Function testColor()
MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays
"#VALUE!"
in the calling cell
End Function
This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA function.
Sadly setting the color does not work, while I can perfectly read the
color-code.
Where's the mistake here? I'm using Excel 2007.
|