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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>