Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Cells with Conditional Formats | Excel Discussion (Misc queries) | |||
Sorting Cells with Conditional Formats | Excel Discussion (Misc queries) | |||
Conditional Formats to Find Words in Text String | Excel Worksheet Functions | |||
conditional formats for cells | Excel Worksheet Functions | |||
Is it possible to do a Find/Replace on Conditional Formats? | Excel Worksheet Functions |