Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Chip's example about vatPtr & objPtr | Excel Programming |