Macro to Find Cells with Conditional Formats
 

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.

Macro to Find Cells with Conditional Formats
 

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(xlCellTypeAllFormatConditi ons)
....
Bob Umlas
Excel MVP

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

Macro to Find Cells with Conditional Formats
 

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


-----Original Message-----
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.
.

Macro to Find Cells with Conditional Formats
 

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

--
Regards,
Tom Ogilvy

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

Macro to Find Cells with Conditional Formats
 

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




-----Original Message-----
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

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


.

Macro to Find Cells with Conditional Formats
 

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

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

--
Regards,
Tom Ogilvy

"John Franklin" wrote in message
...

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




-----Original Message-----
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

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


.

Macro to Find Cells with Conditional Formats
 

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


-----Original Message-----
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

--
Regards,
Tom Ogilvy

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


.