Conditional Formatting Macro Inserting New Rule in Wrong Place

luv2learn

New Member
Joined
Jul 6, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,538
Office Version
  1. 365
Platform
  1. Windows
It would help if you posted the code. ;)
 

luv2learn

New Member
Joined
Jul 6, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,538
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
VBA Code:
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

New Member
Joined
Jul 6, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you so much Fluff- that count did the trick! Much appreciated.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,538
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,661
Messages
5,549,280
Members
410,907
Latest member
Gan_77
Top