Using if function with cell fill colors
 

I'm trying to develop a way to return a value if a cell or row is a specific
color. Can anyone let me know if there is a way to write an IF formula that
will accomplish this, or do I need to write VBA code (of which it has been
years since I've remotely played with.)

Thanks for any help!
Pam

Using if function with cell fill colors
 

You need VBA

Function ColorIndex(rng as range)
if rng.Count 1 Then
ColorIndex = CVErr(xlErrRef)
Else
ColorIndex = rng.Interior.Colorindex
End If
End Function

To create this, go to the VBIDE, Alt-F11, menu InsertModule, and copy the
code.

Back in Excel, use like

=IF(Colorindex(A1)=3,"Red","")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Pam" wrote in message
...
I'm trying to develop a way to return a value if a cell or row is a
specific
color. Can anyone let me know if there is a way to write an IF formula
that
will accomplish this, or do I need to write VBA code (of which it has been
years since I've remotely played with.)

Thanks for any help!
Pam

Using if function with cell fill colors
 

Thanks Bob -

Unfortunately I may be missing something. I'm getting compile errors with
this code. Any thoughts I might want to check into?

Thanks again!!!

"Bob Phillips" wrote:

You need VBA

Function ColorIndex(rng as range)
if rng.Count 1 Then
ColorIndex = CVErr(xlErrRef)
Else
ColorIndex = rng.Interior.Colorindex
End If
End Function

To create this, go to the VBIDE, Alt-F11, menu InsertModule, and copy the
code.

Back in Excel, use like

=IF(Colorindex(A1)=3,"Red","")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Pam" wrote in message
...
I'm trying to develop a way to return a value if a cell or row is a
specific
color. Can anyone let me know if there is a way to write an IF formula
that
will accomplish this, or do I need to write VBA code (of which it has been
years since I've remotely played with.)

Thanks for any help!
Pam

Using if function with cell fill colors
 

Hi Pam,

Bob's function worked for me.

Did you copy / paste Bob's code in order to eliminate possible transcription
errors?

What errors are you getting and which code line is highlighted?

---
Regards,
Norman



"Pam" wrote in message
...
Thanks Bob -

Unfortunately I may be missing something. I'm getting compile errors with
this code. Any thoughts I might want to check into?

Thanks again!!!

"Bob Phillips" wrote:

You need VBA

Function ColorIndex(rng as range)
if rng.Count 1 Then
ColorIndex = CVErr(xlErrRef)
Else
ColorIndex = rng.Interior.Colorindex
End If
End Function

To create this, go to the VBIDE, Alt-F11, menu InsertModule, and copy
the
code.

Back in Excel, use like

=IF(Colorindex(A1)=3,"Red","")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Pam" wrote in message
...
I'm trying to develop a way to return a value if a cell or row is a
specific
color. Can anyone let me know if there is a way to write an IF formula
that
will accomplish this, or do I need to write VBA code (of which it has
been
years since I've remotely played with.)

Thanks for any help!
Pam

Using if function with cell fill colors
 

bob's works ok here.

here's sample vba code if you want to try it

Sub test()
Range("a1").Interior.ColorIndex = 3
If Range("A1").Interior.ColorIndex = 3 Then
ActiveCell.Value = "red"

End If
End Sub
--


Gary


"Pam" wrote in message
...
Thanks Bob -

Unfortunately I may be missing something. I'm getting compile errors with
this code. Any thoughts I might want to check into?

Thanks again!!!

"Bob Phillips" wrote:

You need VBA

Function ColorIndex(rng as range)
if rng.Count 1 Then
ColorIndex = CVErr(xlErrRef)
Else
ColorIndex = rng.Interior.Colorindex
End If
End Function

To create this, go to the VBIDE, Alt-F11, menu InsertModule, and copy the
code.

Back in Excel, use like

=IF(Colorindex(A1)=3,"Red","")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Pam" wrote in message
...
I'm trying to develop a way to return a value if a cell or row is a
specific
color. Can anyone let me know if there is a way to write an IF formula
that
will accomplish this, or do I need to write VBA code (of which it has been
years since I've remotely played with.)

Thanks for any help!
Pam