Conditional Formatting Macro Inserting New Rule in Wrong Place

[luv2learn]

I have lots of conditional formatting rules that are applied through a macro. In the set of rules in conditional formatting they are in the right order. When I run another macro which creates another conditional formatting rule it places that new rule in the middle of the set of rules in conditional formatting and that screws up the previous macros formatting. Is there a way through coding in VBA directly to place the new rule created by the macro at the end of the list of rules?

 

[Fluff]

It would help if you posted the code.

 

[luv2learn]

Thank you for your reply Fluff. I am not sure if this code will help-these are the last macros I run. I am not sure what I did (I think I separated the two conditional formatting rules I had in one macro to two separate macros) but it does apply these conditional formatting rules to the top of my conditional formatting list. I just have to move them to the bottom of the conditional formatting list each time I run the code. I am wondering if there is a way to automatically insert this new formatting to the end of the rules. I can not seem to make a new macro which goes through the steps of moving the conditional formatting rules to the end- it just doesnt save anything. Thanks so much for any help.

Sub HighlightEarnings2()
'
' HighlightEarnings2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
    Range("A2:R400").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$D2=""Y"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub
Sub HighlightEarnings3()
'
' HighlightEarnings3 Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'
    Range("A2:R400").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($D2=""Y"",$E2=""N"")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 12579825
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[Fluff]

Thanks for that, how about

Sub HighlightEarnings2()
'
' HighlightEarnings2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
   With Range("A2:R400")
      .FormatConditions.Add Type:=xlExpression, Formula1:="=$D2=""Y"""
      With .FormatConditions(.FormatConditions.Count).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
      End With
      .FormatConditions(.FormatConditions.Count).StopIfTrue = False
   End With
End Sub
Sub HighlightEarnings3()
'
' HighlightEarnings3 Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'
   With Range("A2:R400")
      .FormatConditions.Add Type:=xlExpression, Formula1:= _
         "=AND($D2=""Y"",$E2=""N"")"
      With .FormatConditions(.FormatConditions.Count).Interior
          .PatternColorIndex = xlAutomatic
          .Color = 12579825
          .TintAndShade = 0
      End With
      .FormatConditions(.FormatConditions.Count).StopIfTrue = False
   End With
End Sub

Be warned that if you are running this on the same sheet regularly, unless you delete the existing rules first you will end up with multiple rules & risk crashing Excel.

 

[luv2learn]

Thank you so much Fluff- that count did the trick! Much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.