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

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