Chip's ColorIndexOfCF Function

[John]

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]

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

 

[Chip Pearson]

John,

The ColorIndexOfCF is a normal VBA function, not a worksheet
function. Thus, you don't call it as a method of your fn
variable.

Change

If fn.ColorIndexOfCF(Rng1) = 6 Then
' to
If ColorIndexOfCF(Rng1) = 6 Then

Secondly, you are probably passing the wrong variable to the
ColorIndexOfCF function. Pass Cell rather than Rng1


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[John]

Chip, thanks for the response - got it to work.

By the way, thanks for your site. It's a great reference
tool.

John Mansfield

 

[John]

Dave,

Thanks for your reponse - I got it to work.

John Mansfield