Conditional Formatting issues with VBA insert/delete row

[Philbanks]

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:

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

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

 

[rlv01]

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.

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]

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.

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]

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.

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]

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.

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