View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
AltaEgo AltaEgo is offline
external usenet poster
 
Posts: 245
Default Set cell color with VBA results in "#VALUE!"

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.