[billQ]

Hello,
Is it possible to change a cell interior color within a function or must
that be done throught a sub.
thanks
billq

[patrick molloy]

you can do it in code, or use Conditional Formatting -
though this only allows three conditions.

Sub ColorTest()
Dim i As Long
On Error Resume Next
Do
Cells(i + 1, 1) = i
Cells(i + 1, 2).Interior.ColorIndex = i
i = i + 1
Loop While Err.Number = 0

End Sub

Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Hello,
Is it possible to change a cell interior color within a
function or must
that be done throught a sub.
thanks
billq


.

[Dave[_15_]]

Hi Bill,

No. A function cannot change the contents or format of a cell.

Regards

Dave


"billQ" wrote in message
...
Hello,
Is it possible to change a cell interior color within a function or must
that be done throught a sub.
thanks
billq

[Bob Phillips[_5_]]

Bill,

Why not use CF as Patrick suggests.

With A1:A4 selected, go to FormatConditional Formatting
For Condition1
Choose FormulaIs
Add a formula of =MOD(ROW(),2)=0
Select the format button, pattern tab an d set the colour
OK
For Condition2, repeat with a formula of =MOD(ROW(),2)=1 and a different
colour

This CF can be copied as the range changes

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"billQ" wrote in message
...
Hi Patrick,
I can do the change of color through a sub. I have a formula which
calculates the sum of a range, where I would like to alternate the colors
of
the range after summing. The range is dynamic so I was planning on
alternating the colors within the function.

Function myFunc() As Boolean
Dim rgn As Range
Dim color As Boolean
color = True
Set rgn = Range("A1:A4")
For Each cell In rgn
If color = True Then
cell.Interior.ColorIndex = 15
color = False
ElseIf color = False Then
cell.Interior.ColorIndex = 2
color = True
End If
Next cell
my = color
End Function
The code does not affect the cell within the range when I place =myFunc()
in
any cell.
thanks
billq
"billQ" wrote in message
...
Hello,
Is it possible to change a cell interior color within a function or must
that be done throught a sub.
thanks
billq