I've a simple function, copied from various examples on the net, to sum
numbers based on the color of another cell. I want change the active cell
color but it doesn't seem to work. Any suggestions, any help appreciated,
code is as follows...

Function ColorSum(cRefColor, rRange)
Dim r As Range
Dim iColorIndex As Integer

ColorSum = 0

iColorIndex = cRefColor.Font.ColorIndex

'build formula for range
For Each C1 In rRange.Cells
If C1.Font.ColorIndex = iColorIndex Then
ColorSum = ColorSum + C1.Value
Else
C1.Font.ColorIndex = iColorIndex
End If
Next

'this doesn't work!
ActiveCell.Font.ColorIndex = iColorIndex

End Function

It won't work if you are calling the function from a worksheet cell.
Functions called from worksheet cells cannot change any part of the Excel
environment, including changing the attributes of cells.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Peter Wells" <Peter wrote in message
...
I've a simple function, copied from various examples on the net, to sum
numbers based on the color of another cell. I want change the active cell
color but it doesn't seem to work. Any suggestions, any help appreciated,
code is as follows...

Function ColorSum(cRefColor, rRange)
Dim r As Range
Dim iColorIndex As Integer

ColorSum = 0

iColorIndex = cRefColor.Font.ColorIndex

'build formula for range
For Each C1 In rRange.Cells
If C1.Font.ColorIndex = iColorIndex Then
ColorSum = ColorSum + C1.Value
Else
C1.Font.ColorIndex = iColorIndex
End If
Next

'this doesn't work!
ActiveCell.Font.ColorIndex = iColorIndex

End Function

Peter

You can't make alterations with a function - functions can only retur
a value. You need a Sub procedure to change the font colour.

Best regards

Richar

--
RichardScholla
-----------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=524
View this thread: http://www.officehelp.in/showthread.php?t=126977

Posted from - http://www.officehelp.i

Thanks for the replies,

Peter

"RichardSchollar" wrote:


Peter

You can't make alterations with a function - functions can only return
a value. You need a Sub procedure to change the font colour.

Best regards

Richard


--
RichardSchollar
------------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=5248
View this thread: http://www.officehelp.in/showthread.php?t=1269777

Posted from - http://www.officehelp.in