Setting ActiveCell color

[Guest]

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

 

[Chip Pearson]

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)

 

[Guest]

Thanks for the replies,

Peter