Chip's ColorIndexOfCF Function
 

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

Chip's ColorIndexOfCF Function
 

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

Chip's ColorIndexOfCF Function
 

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

Chip's ColorIndexOfCF Function
 

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

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

John Mansfield

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




.

Chip's ColorIndexOfCF Function
 

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

.