Dave,

Thanks for your reponse - I got it to work.

John Mansfield

-----Original Message-----
I'm not sure what X and Y are, but I think you just made
a typo:

Sub Test()

Set Rng1 = Range("B4:B10")
Set Rng2 = Range("F4")
Set fn = Application.WorksheetFunction

For Each Cell In Rng1
If fn.ColorIndexOfCF(Cell) = 6 Then '<--Rng1
changed to Cell
Cell.Copy
Rng2.PasteSpecial Paste:=xlValues
Set Rng2 = Rng2.Offset(1, 0)
End If
Next Cell

End Sub

John wrote:

I've added Chip Pearson's ColorIndexOfCF Function to the
following conditional formatting code below.

I'm getting an "object doesn't support property or
method
error" on the following line:

" If fn.ColorIndexOfCF(Rng1) = 6 Then "

What I'm trying to do is loop through a range and
say "If
the conditional formatting color index = 6 (yellow)
then "X" else "Y".

How can I restructure the code below to make it work?

Sub Test()

Set Rng1 = Range("B4:B10")
Set Rng2 = Range("F4")
Set fn = Application.WorksheetFunction

For Each Cell In Rng1
If fn.ColorIndexOfCF(Rng1) = 6 Then
Cell.Copy
Rng2.PasteSpecial Paste:=xlValues
Set Rng2 = Rng2.Offset(1, 0)
End If
Next Cell

End Sub

Chip's function reference:
http://www.cpearson.com/excel/CFColors.htm

--

Dave Peterson

.