setting cell color
 

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

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

"Wazooli" wrote:

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

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

--
HTH

Bob Phillips

"Wazooli" wrote in message
...
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

"Wazooli" wrote:

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

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

Wazooli

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"Wazooli" wrote in message
...
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

"Wazooli" wrote:

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

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

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

"Wazooli" wrote:

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

Wazooli

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"Wazooli" wrote in message
...
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

"Wazooli" wrote:

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

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)



"Wazooli" wrote in message
...
So then there is no way to assign a color to a cell using VBA?

Wazooli

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"Wazooli" wrote in message
...
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

"Wazooli" wrote:

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

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

"Peo Sjoblom" wrote:

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)



"Wazooli" wrote in message
...
So then there is no way to assign a color to a cell using VBA?

Wazooli

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"Wazooli" wrote in message
...
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

"Wazooli" wrote:

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

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

See my reply to Waxooli.

--
HTH

Bob Phillips

"Ola" wrote in message
...
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

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

"Ola" wrote:

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