Updating Conditional formatting using VBA adds in conditions rather than replacing them

[miless2111s]

I have a sheet where users will sometimes paste in elements. In doing so they can replace the conditional formatting in either nothing or some other formats.

I have some VBA on a button designed to replace the formatting with the "correct" conditional formatting (see below). The issue I am facing is that it adds conditions rather than replacing them. This means that after a while, we have lots of conditions, most of them repeats, which can make troubleshooting a bit tricky. I have around 15 of these expressions (I have included a small number for illustration), so I am keen to avoid complexity if possible.

    'D: Check that item in date range  Rnage: =$D$3:$D$302,$D$305:$D$330,$D$333:$D$353,$D$356:$D$406   Condition: =AND(D3="y",OR(VLOOKUP(A3,MS_Data,3,FALSE)<Rep_start,VLOOKUP(A3,MS_Data,3,FALSE)>Rep_End))
    With Worksheets("Bar Details").Range("$D$3:$D$302,$D$305:$D$330,$D$333:$D$353,$D$356:$D$406").FormatConditions.Add(Type:=xlExpression, Formula1:= _
        "AND(D3=""y"",OR(VLOOKUP(A3,MS_Data,3,FALSE)<Rep_start,VLOOKUP(A3,MS_Data,3,FALSE)>Rep_End))")
            .Font.Bold = False
            .Interior.ColorIndex = 15
            .StopIfTrue = False
    End With
    'd: Check that item has a "Y" when it is in the data  Range: =$D$3:$D$302,$D$305:$D$330,$D$333:$D$353,$D$356:$D$406  Condition: =AND(D3="",NOT(ISERROR(VLOOKUP(A3,MS_Data,1,FALSE))))  Amber
    With Worksheets("Bar Details").Range("$D$3:$D$302,$D$305:$D$330,$D$333:$D$353,$D$356:$D$406").FormatConditions.Add(Type:=xlExpression, Formula1:= _
        "=AND(D3="""",NOT(ISERROR(VLOOKUP(A3,MS_Data,1,FALSE))))")
            .Font.Bold = False
            .Interior.ColorIndex = 45
            .StopIfTrue = False
    End With
    'D: check if the item has a "Y" and it isn't in the data Range:  =$D$3:$D$302,$D$305:$D$330,$D$333:$D$353,$D$356:$D$406  Condition =AND(NOT(D3=""),ISERROR(VLOOKUP(A3,MS_Data,1,FALSE)))  Red
    With Worksheets("Bar Details").Range("$D$3:$D$302,$D$305:$D$330,$D$333:$D$353,$D$356:$D$406").FormatConditions.Add(Type:=xlExpression, Formula1:= _
        "=AND(NOT(D3=""""),ISERROR(VLOOKUP(A3,MS_Data,1,FALSE)))")
            .Font.Bold = False
            .Interior.ColorIndex = 3
            .StopIfTrue = False
    End With

I have one solution, which is to add the same range before each one with .FormatConditions.Delete so something like below however I don't know if this is the best / most efficient way to do it

    Worksheets("Bar Details").Range("$D$3:$D$302,$D$305:$D$330,$D$333:$D$353,$D$356:$D$406").FormatConditions.Delete
    With Worksheets("Bar Details").Range("$D$3:$D$302,$D$305:$D$330,$D$333:$D$353,$D$356:$D$406").FormatConditions.Add(Type:=xlExpression, Formula1:= _
        "AND(D3=""y"",OR(VLOOKUP(A3,MS_Data,3,FALSE)<Rep_start,VLOOKUP(A3,MS_Data,3,FALSE)>Rep_End))")
            .Font.Bold = False
            .Interior.ColorIndex = 15
            .StopIfTrue = False
    End With

Any help will be appreciated.

 

[RoryA]

You could modify the existing formats (assuming you know that all those cells will definitely already have them) but that delete and reapply approach is the simplest in my opinion.

 

[miless2111s]

You could modify the existing formats (assuming you know that all those cells will definitely already have them) but that delete and reapply approach is the simplest in my opinion.

Is there any advantage (speed etc) to doing something like Worksheets("Bars Details").Cells.FormatConditions.Delete?

 

[RoryA]

I very much doubt that there would be any noticeable difference, but you could always try it.

 

[miless2111s]

Thanks