Macro to Find Cells with Conditional Formats

  • Thread starter Thread starter John Franklin
  • Start date Start date
J

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.
 
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
 
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
 
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
 
see my post - you are headed down the wrong road.

The answers you received didn't seem to understand the question.
 
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
 
Back
Top