Can i get existing conditional formatting into a macro?

[tonywatsonhelp]

Hi everyone,
I have used the macro recorder in the past to get conditional formatting into vba, but I can only get it to work when i create anew rule,

is there a way to extract all conditioning formatting rules into vba so i can run them and edit them etc.

any ideas would be helpful

Thanks

Tony

 

[Fluff]

Probably simpler to just get a macro to delete all the rules & create the new ones.

 

[mikerickson]

You can probe Conditional Formatting with code like this. But some of the properties values can be a little obscure.
What do you mean by running a conditional format?

Sub test()
    Dim oneCell, oneCondition As FormatCondition
    For Each oneCell In ActiveSheet.UsedRange
        With oneCell
            If .FormatConditions.Count > 0 Then
                For Each oneCondition In .FormatConditions
                    MsgBox oneCondition.Formula1 & " applies to " & oneCondition.AppliesTo.Address
                Next oneCondition
            End If
        End With
    Next
End Sub

 

[tonywatsonhelp]

HI Thanks for your help,
what I'm trying to do is get all my conditional formatting rules into a macro,
if i was to create them now, I could record the rule being created with the macro recorder, but as they are already in there i cant seam to get to them, if i try editing the rule with the macro recorder on nothing get recorded.

basically my conditional formatting keeps getting massed up when rows, ranges cells are copy pasted, inserted etc, so what ive done in the past if set up my macro to reset all conditions as i want them and just run it at set times (after a copy and paste for example, this idea works great but i have about 50 rules so getting them into a macro means retyping them all with the macro recorder on. before i do this and spend a day or two on it i was wondering if there is a way to get the rulles as they are all in the sheet exactly as i want them now, and put them into a macro with vba? any help would be appreciated.

Thanks

 

[Fluff]

There's no way I know of, that will create a macro with your CF formula, ranges etc.
You could use the code from mikerickson to find out what the formats are, but you would still need to create another macro to apply them.

 

[mikerickson]

If that is the problem, what I join Fluff in suggesting that you record yourself
1) clearing all CF
2) setting up the CF the way that you want it.

 

[tonywatsonhelp]

Hi Guys,
Oh well, looks like it a weekend of slogging away then lol
thanks for your help
Tony

 

[Fluff]

Glad we could help (even if it wasn't what you wanted the hear) & thanks for the feedback.

 

[SLCDrafter]

I know this is an old post, but here is how I accomplish this.

Start recording macro → Conditional Formatting Manage Rules → Create a bogus rule with a formatting → Apply → Delete Rule → Click OK → Stop recording Macro. All rules are now listed in macro you have just recorded.

 

[tonywatsonhelp]

I know this is an old post, but here is how I accomplish this.

Start recording macro → Conditional Formatting Manage Rules → Create a bogus rule with a formatting → Apply → Delete Rule → Click OK → Stop recording Macro. All rules are now listed in macro you have just recorded.

Brilliant thank you