Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior.Color from a function yields Error 1004
In Excel 2007 I am trying to change the Fill color of a cell from a macro. I
am fairly adept with excel macros. So here is the problem with a simple example. Sub FormatResult() Range("A1").Interior.Color = 255 '*** This Works End Sub Add a formula to a cell e.g. =FormatResultByRange(A1) Function FormatResultByRange(r as Range) r.Interior.Color = 255 '*** Error 1004 End function alternatively... Function FormatResultByRange(r as Range) ' r is ignored and not used Range("A1").Interior.Color = 255 '*** Error 1004 End function It doesn't seem to work when executed via a formula. Code In Context: I know the follwoing example can be achieved using coditional formatting, but the real code is actually very complex involving 3d math etc. But what is below illustrates the problem in context. n.b. cell formula: = FormatAge() Function FormatAge() Dim r as Range Dim age as Integer Dim fmtcolor as Variant Set r = Application.Caller age = r.Value if(age<16) then fmtcolor = &H0000FF else if(age<18) then fmtcolor=&HFF8000 else fmtcolor=&H00FF00 end if end if r.Interior.Color = fmtColor '*** Error 1004 End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior.Color from a function yields Error 1004
You've discovered one of the limitations of functions -- a UDF cannot
do anything to the worksheet. A function can return a value as the result of a calculation, but you can never do something that would affect the worksheet such as changing colors, Check out this KB: http://support.microsoft.com/kb/170787 HTH, JP On Jan 22, 9:06*pm, Diaren Whan <Diaren wrote: In Excel 2007 I am trying to change the Fill color of a cell from a macro. I am fairly adept with excel macros. So here is the problem with a simple example. Sub FormatResult() * Range("A1").Interior.Color = 255 *'*** This Works End Sub Add a formula to a cell e.g. =FormatResultByRange(A1) Function FormatResultByRange(r as Range) * r.Interior.Color = 255 '*** Error 1004 End function alternatively... Function FormatResultByRange(r as Range) * ' r is ignored and not used * Range("A1").Interior.Color = 255 '*** Error 1004 End function It doesn't seem to work when executed via a formula. Code In Context: I know the follwoing example can be achieved using coditional formatting, but the real code is actually very complex involving 3d math etc. But what is below illustrates the problem in context. n.b. cell formula: *= FormatAge() Function FormatAge() * Dim r as Range * Dim age as Integer * Dim fmtcolor as Variant * Set r = Application.Caller * age = r.Value * if(age<16) then * * *fmtcolor = &H0000FF * else * * *if(age<18) then * * * * fmtcolor=&HFF8000 * * *else * * * * * fmtcolor=&H00FF00 * * *end if * end if * r.Interior.Color = fmtColor '*** Error 1004 End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Error 1004 Unable to set the ColorIndex property of the Interior. | Excel Programming | |||
run-time error '1004': unable to set the ColorIndex property of the Interior class | Excel Programming | |||
setting interior color - error | Excel Programming | |||
Need Excel Formula/Function to color cell interior | Excel Worksheet Functions | |||
Interior class error/runtime error 1004 | Excel Programming |