Macro to Find Cells with Conditional Formats

[John Franklin]

I have two lists of data that I'm comparing using
conditional formatting. I want to create a list of all
values that have been flagged by the conditional
formatting. An example of the macro that I'm using is
below. How do I get the macro to pick up values flagged
by the conditional formatting?

Sub CompareLists()
Dim Rng1 As Range
Dim Rng2 As Range
Set Rng2 = Range("E3")
For Each Rng1 In Range("B3", "C10")
If Rng1.FormatConditions(1).Interior.ColorIndex = 6
Then
Rng2.Value = Rng1.Value
Set Rng2 = Rng2.Offset(1, 0)
End If
Next Rng1
End Sub

Thanks for your help.

 

[Bob Umlas]

Try recording Edit/Goto Special, select "Conditional Formats".
You'll get something like:
Cells.SpecialCells(xlCellTypeAllFormatConditions).Select

So, change your macro to
For Each Rng1 In Range("B3",
"C10").SpecialCells(xlCellTypeAllFormatConditions)
....
Bob Umlas
Excel MVP

 

[GJones]

Hi John;

If you will apply the conditional formatting to each cell
instead of the range you can use the count property to
determine if it is true. You can use something like this;


MyConditionalCount = ActiveCell.FormatConditions.Count

Thanks,

Greg

 

[Tom Ogilvy]

If you want to check the color produced by conditional formatting, you can't
do it directly. There is no property that reveals that. Chip Pearson has
code that checks the conditions of the conditional format to see if they are
met:

http://www.cpearson.com/excel/CFColors.htm

 

[John Franklin]

I still seem to be picking up all values with conditional
formatting as opposed to just the ones that have been
flagged yellow. Here is my code now. What could I be
doing wrong? Thanks again.

Sub CompareLists()
Dim Rng1 As Range
Dim Rng2 As Range
Set Rng2 = Range("E3")
For Each Rng1 In Range("B3", "C10").SpecialCells
(xlCellTypeAllFormatConditions)
If Rng1.FormatConditions(1).Interior.ColorIndex = 6
Then
Rng2.Value = Rng1.Value
Set Rng2 = Rng2.Offset(1, 0)
End If
Next Rng1
End Sub

 

[Tom Ogilvy]

see my post - you are headed down the wrong road.

The answers you received didn't seem to understand the question.

 

[John Franklin]

Tom,

Thank you very much for your help with this. I think
Chip's page will probably help me solve the problem.

Thanks again.

jf