Set cell color with VBA results in "#VALUE!"
First, a change in the color of a cell does not cause a re-calculate to occur
on the worksheet. If you changed the value in the cell then the function ill
be executed.
Second, You should not reference a worksheet cell from inside a function.
If you want to use the value in A1 then pass A1 as a parameter
Public Function testColor(Target as range)
MsgBox Target.Interior.Color 'does work - displays e.g. 255
Target.Interior.Color = 10 'does NOT work - displays "#VALUE!"
'in the calling cell
End Function
call function from spreadsheet with
=testcolor(A1)
Third, the code above will not work. A function can only return a value
(not a color change) to the cell where the function is located and not a
different cell. Sub can change any cell but must be manually executed or use
an event.
You best choice might be to use a worksheet change event. Not sure what you
are really trying to do.
"Christian Schratter" wrote:
Well, I already tried that once, and just redid it to verify it, but
unfortuantely using ColorIndex instead of Color does not change anything. :-(
"Mike H" wrote:
Try these
MsgBox Cells(1, 1).Interior.ColorIndex
Cells(1, 1).Interior.ColorIndex = 10
Mike
"Christian Schratter" wrote:
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.
|