setting cell color

[Guest]

I have been using the following code, as posted in this usergroup recently:

Public Function GetCellColor(MyCell As Range) As Variant
GetCellColor = MyCell.Interior.ColorIndex
End Function

This works great, and allows me to use the cell color as a downstream
condition. Is there a similarly succint bit of code that will allow me to
assign a color to a cell via a UDF?

Thanks,

Wazooli

 

[Guest]

And, P.S. - I don't want to do this via conditional formatting. I would like
to learn how to do it with VBA.

Wazolli

 

[Bob Phillips]

No, because UDFS return a value, they do not change worksheet attributes.

 

[Guest]

So then there is no way to assign a color to a cell using VBA?

Wazooli

 

[Guest]

Sub Color_red()
'
' Color_red Macro
' Set cell color to red
'

'
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

 

[Peo Sjoblom]

That's not what you asked and what Bob answered to, you asked if it was
possible using a UDF
to use a macro see:

http://www.mvps.org/dmcritchie/excel/colorrng.htm

http://www.mvps.org/dmcritchie/excel/colors.htm

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Guest]

Thanks Peo. I will try to use some of what is on the second link you
supplied, although you shouldn't be surprised if I am back here asking
questions.

wazooli

 

[Guest]

Is it possible to make this work?

Public Function ColorCell(MyCell As Range, Color, Optional Message) As Variant
MyCell.Interior.ColorIndex = Color
If IsEmpty(Message) Then ColorCell = "" Else ColorCell = Message
End Function

Ola

 

[Bob Phillips]

See my reply to Waxooli.

 

[Guest]

Yes (with slight correction):

Public Function ColorCell(MyCell As Range, Color, Optional Message) As Variant
MyCell.Interior.ColorIndex = Color
If IsEmpty(Message) Then MyCell = "" Else MyCell = Message
End Function
Sub Color_red()
'
' Color_red Macro
' Set cell color to red
'

'
Dim x As Variant
x = ColorCell(Range("a2:a7"), 3, "This should be red")
End Sub