Conditional Formatting issues with VBA insert/delete row

Philbanks

New Member
Joined
Sep 4, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
Hi,

I’m hoping someone can help…I’ve read through quite a lot of threads and see that what I’m struggling with seems like a common thing…but I still can’t figure it out….I think my mind is zonked as I’m not a regular Excel user.

Quick outline:
I'm struggling to add/keep conditional formatting rules after adding or deleting rows through VBA script.
I’d love to have some guidance, or point me in the direction of solutions (I've tried, but struggled)

More info:
I have a spreadsheet in excel that is working pretty much as I'm needing...except one thing...I can't workout how to keep the conditional formatting working.
I can get conditional formatting to work on the sheet as wanted, but when I insert or delete a row through VBA, the conditional formatting doesn't copy/extend. So, what I'm after is once I add/delete a row with the buttons I’ve made, the script will automatically update the conditional formatting rules. I’ve seen many posts point to the solution of deleting any conditional formatting on the range of cells and then re-applying the rules…My brain is so small and fried, I can’t workout how to do for my scenario…

Here are the formulas I'm using for the conditional formatting:

Rich (BB code):
Warning Format
[$H$21:$H$(Last Row minus 1 (LR))] =AND($E21="Message (additional)",$H21<>"")
[$I$21:$I$(LR)] =AND($E21<>"Intervals",$I21<>"")
[$G$21:$G$(LR)] =AND($E21<>"Intervals",$G21<>"")
[$F$21:$F$(LR)] =AND($E21<>"Free Ride",$F21<>"")
[$L$21:$L$(LR)] =AND($E21<>"Message (additional)",$L21<>0)
[$L$22:$L$(LR)] =AND($E22="Message (additional)",$L22=0)
[$L$22:$L$(LR)] =AND($E22="Message (additional)",($L22/86400)>=$AC22)
[$L$22:$L$(LR)] =AND($E22="Message (additional)",$E21="Message (additional)",$L22<=$L21)
[$K$21:$K$(LR)] Duplicate Values

Blocker Format
[$P$21:$Q$(LR),$U$21:$U$(LR),$W$21:$W$(LR),$Y$21:$Y$(LR),$AA$21:$AA$(LR)] =OR($E21="Free Ride",$E21="Constant")
[$G$21:$G$(LR),$I$21:$I$(LR),$S$21:$S$(LR)] =$E21<>"Intervals"
[$H$21:$H$(LR),$N$21:$R$(LR),$T$21:$AA$(LR)] =$E21="Message (additional)"
[$F$21:$F$(LR)] =$E21<>"Free Ride"

And this (I think...as I'm really not sure what I'm doing) is how I want the the different things formatted

Rich (BB code):
Warning
(Basically red fill with white text)
With XXXXRANGEXXXX.FormatConditions(1).Font
        .Color = 16777215
    End With
    With XXXX.FormatConditions(1).Interior
        .Color = 192
End With

Blocker:
(Basically, dark grey fill, black diagnal pattern, black text)
With XXXXRANGEXXXX
        .FormatConditions(1).Font
        .Color = 0
    .FormatConditions(1).Interior
        .Color =  15204351
        .PatternColorIndex = -4105
        .TintAndShade = 0
        .Pattern = 1
End With

Alternate Row change:
Haven't found the code for this yet

Here's a link to >>>the workbook<<< (it's the first sheet) so you can see what I'm trying to do. Any help would be hugely appreciated (and I've no doubt that there are better ways of doing what I've done formula wise...but it works for what I need

Big thanks in advance
Phil
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,159
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
As you noted
...I’ve seen many posts point to the solution of deleting any conditional formatting on the range of cells and then re-applying the rules

That was good advice. I don't really want to wade into that complicated mess of ranges and formulas you have, but here is a 'how-to' example for one such range.

VBA Code:
Sub SetFormatConditionsExample()
    Dim FCRange As Range
    Dim FormulaStr As String, DQ As String
    Dim WS As Worksheet
    
    Set WS = ActiveSheet                    'define worksheet containing format conditions.
    
    With WS
        Set FCRange = .Range("I21:I" & .Range("I" & .Rows.Count).End(xlUp).Row) 'define the range of cells you want to 'refresh'
    End With
    
    With FCRange.FormatConditions
        .Delete                             'Delete existing conditional formatting. It is important to clear existing format conditions in the designated range each time the macro is run
        
        DQ = """" 'double quote
        
        'Construct the needed formula, i.e.  =AND($E21<>"Intervals",$I21<>"")
        FormulaStr = "=AND($E21<>" & DQ & "Intervals" & DQ & ",$I21<>" & DQ & "" & DQ & ")"         'Any formula that resolves to True or False"
        
        'Set format condition for FCRange
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = vbBlack
        End With
    End With
End Sub
 

Philbanks

New Member
Joined
Sep 4, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
As you noted


That was good advice. I don't really want to wade into that complicated mess of ranges and formulas you have, but here is a 'how-to' example for one such range.

VBA Code:
Sub SetFormatConditionsExample()
    Dim FCRange As Range
    Dim FormulaStr As String, DQ As String
    Dim WS As Worksheet
   
    Set WS = ActiveSheet                    'define worksheet containing format conditions.
   
    With WS
        Set FCRange = .Range("I21:I" & .Range("I" & .Rows.Count).End(xlUp).Row) 'define the range of cells you want to 'refresh'
    End With
   
    With FCRange.FormatConditions
        .Delete                             'Delete existing conditional formatting. It is important to clear existing format conditions in the designated range each time the macro is run
       
        DQ = """" 'double quote
       
        'Construct the needed formula, i.e.  =AND($E21<>"Intervals",$I21<>"")
        FormulaStr = "=AND($E21<>" & DQ & "Intervals" & DQ & ",$I21<>" & DQ & "" & DQ & ")"         'Any formula that resolves to True or False"
       
        'Set format condition for FCRange
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = vbBlack
        End With
    End With
End Sub
I think this makes sense!! thanks so much for replying and helping rlv01.

I've got a couple of follow up questions if that's ok :)
  1. I'm not sure where/how I would add the additional conditional formats and formulas - could you add a comment/mark on how to do that please? Or would it be easier/better practice to do each one as seperate macro/script? (and if the seperate macro/script is the way to go, should I change 'FormulaStr' for each formula, or is that coding term I need keep the same?)
  2. The double quote 'DQ' variable thingy...this might be more of a general question (and i hope variable was the right term); is the 'DQ' needed in addition to any " inside the formula, or instead of? I see you've written:
    • = "=AND($E21<>" & DQ & "Intervals" & DQ & ",$I21<>" & DQ & "" & DQ & ")"...which in my head, converts to: = "=AND($E21<>""""Intervals"""",$I21<>"""""""")"
    • Am I understanding that correctly? (I realise I don't actually write it as my head is converting it, just trying to clarify that I'm understanding what's being written by you correctly)
Thanks so much again :)
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,159
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I've got a couple of follow up questions if that's ok :)
  1. I'm not sure where/how I would add the additional conditional formats and formulas - could you add a comment/mark on how to do that please? Or would it be easier/better practice to do each one as seperate macro/script? (and if the seperate macro/script is the way to go, should I change 'FormulaStr' for each formula, or is that coding term I need keep the same?)
  2. The double quote 'DQ' variable thingy...this might be more of a general question (and i hope variable was the right term); is the 'DQ' needed in addition to any " inside the formula, or instead of? I see you've written:
    • = "=AND($E21<>" & DQ & "Intervals" & DQ & ",$I21<>" & DQ & "" & DQ & ")"...which in my head, converts to: = "=AND($E21<>""""Intervals"""",$I21<>"""""""")"
    • Am I understanding that correctly? (I realise I don't actually write it as my head is converting it, just trying to clarify that I'm understanding what's being written by you correctly)
Thanks so much again :)

1. The pattern is the same, and repeats. a) Define the column range, b) Delete the existing format conditions, c) Define the formula, d) Add the new FormatCondition & define the formatting.
2. Don't depend on your head. Use Debug.Print FormulaStr statements to observe how the DQ variables are used to create the needed formulas . Play around with it to gain an understanding.

VBA Code:
Sub SetFormatConditionsExample()
    Dim FCRange As Range
    Dim FormulaStr As String, DQ As String
    Dim WS As Worksheet
   
    Set WS = ActiveSheet                    'define worksheet containing format conditions.
   
    With WS
        Set FCRange = .Range("I21:I" & .Range("I" & .Rows.Count).End(xlUp).Row) 'define the range of cells you want to 'refresh'
    End With
   
    With FCRange.FormatConditions
        .Delete                             'Delete existing conditional formatting. It is important to clear existing format conditions in the designated range each time the macro is run
       
        DQ = """" 'double quote
       
        'Construct the needed formula, i.e.  =AND($E21<>"Intervals",$I21<>"")
        FormulaStr = "=AND($E21<>" & DQ & "Intervals" & DQ & ",$I21<>" & DQ & "" & DQ & ")"         'Any formula that resolves to True or False"
        Debug.Print FormulaStr

        'Set format condition for FCRange
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = vbBlack
        End With
    End With
   
    ' Next column ------------------------------
   
    'a) Define the column range
    With WS
        Set FCRange = .Range("G21:G" & .Range("G" & .Rows.Count).End(xlUp).Row) 'define the range of cells you want to 'refresh'
    End With
   
    With FCRange.FormatConditions
        'b) Delete the existing format conditions
        .Delete
       
        DQ = """" 'double quote
       
        'c) Define the formula
        'Construct the needed formula, i.e.  =AND($E21<>"Intervals",$G21<>"")
        FormulaStr = "=AND($E21<>" & DQ & "Intervals" & DQ & ",$G21<>" & DQ & "" & DQ & ")"         'Any formula that resolves to True or False"
        Debug.Print FormulaStr
       
        'd) Add the new FormatCondition and define the formatting
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = vbBlack
        End With
    End With
   
    'Next Column----------
    'Repeat as needed
   
End Sub
 

Philbanks

New Member
Joined
Sep 4, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
1. The pattern is the same, and repeats. a) Define the column range, b) Delete the existing format conditions, c) Define the formula, d) Add the new FormatCondition & define the formatting.
2. Don't depend on your head. Use Debug.Print FormulaStr statements to observe how the DQ variables are used to create the needed formulas . Play around with it to gain an understanding.

VBA Code:
Sub SetFormatConditionsExample()
    Dim FCRange As Range
    Dim FormulaStr As String, DQ As String
    Dim WS As Worksheet
  
    Set WS = ActiveSheet                    'define worksheet containing format conditions.
  
    With WS
        Set FCRange = .Range("I21:I" & .Range("I" & .Rows.Count).End(xlUp).Row) 'define the range of cells you want to 'refresh'
    End With
  
    With FCRange.FormatConditions
        .Delete                             'Delete existing conditional formatting. It is important to clear existing format conditions in the designated range each time the macro is run
      
        DQ = """" 'double quote
      
        'Construct the needed formula, i.e.  =AND($E21<>"Intervals",$I21<>"")
        FormulaStr = "=AND($E21<>" & DQ & "Intervals" & DQ & ",$I21<>" & DQ & "" & DQ & ")"         'Any formula that resolves to True or False"
        Debug.Print FormulaStr

        'Set format condition for FCRange
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = vbBlack
        End With
    End With
  
    ' Next column ------------------------------
  
    'a) Define the column range
    With WS
        Set FCRange = .Range("G21:G" & .Range("G" & .Rows.Count).End(xlUp).Row) 'define the range of cells you want to 'refresh'
    End With
  
    With FCRange.FormatConditions
        'b) Delete the existing format conditions
        .Delete
      
        DQ = """" 'double quote
      
        'c) Define the formula
        'Construct the needed formula, i.e.  =AND($E21<>"Intervals",$G21<>"")
        FormulaStr = "=AND($E21<>" & DQ & "Intervals" & DQ & ",$G21<>" & DQ & "" & DQ & ")"         'Any formula that resolves to True or False"
        Debug.Print FormulaStr
      
        'd) Add the new FormatCondition and define the formatting
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = vbBlack
        End With
    End With
  
    'Next Column----------
    'Repeat as needed
  
End Sub
Thanks so much again! It makes sense, and like you say, I'll play about with the debug thing to try and workout what's happening.

Super super helpful and appreciated...Thanks again :)
 

Forum statistics

Threads
1,148,253
Messages
5,745,686
Members
423,968
Latest member
LTCASA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top