Lock or fix conditional formatting?

[knigget]

I have a sheet that information is pasted to from another workbook. On this sheet, I have a macro to delete and sort duplicates upon load. This sheet also contains conditional formatting:

=F3="N/A" (silver)

I have applied that to F3:F50. That is 48 lines that contain the CF.

The problem I have is that when the macro is run more than 48 times, my conditional formatting has all gone! To clarify, if I run the macro 3 times, I have 45 lines remaining that contain my CF - if I run the macro 10 times I have 38 lines remaining etc...
Is there a way of locking the CF so it is always on cells within the range of F3 to F50? I do not want to CF the entire column (upto 65536) as my file size increases drastically, obviously so too does the loading time.

Any ideas?

 

[Peter_SSs]

I'm about to sign off for the night but is this the summary?

F: silver if it is "N/A"
G: silver if F is "N/A otherwise red if G contains a date < today

H: silver if it is "N/A"
I: silver if H is "N/A otherwise red if I contains a date < today

J: silver if it is "N/A"
K: silver if J is "N/A otherwise red if K contains a date < today

etc across the columns?


You should not need 3 conditions for column G, I, J, ....
The second (red) condition could just be changed to
=AND(G3< TODAY(),G3>0)

 

[AlphaFrog]

    Application.ReferenceStyle = xlR1C1
    For i = 0 To 34 Step 2
        With Range("F3:F50").Offset(, i)
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=RC=""N/A"""
            .FormatConditions(1).Interior.ColorIndex = 15
            With .Offset(, 1)
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlExpression, Formula1:="=RC[-1]=""N/A"""
                .FormatConditions(1).Interior.ColorIndex = 15
                .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(RC >0, RC< TODAY())"
                .FormatConditions(2).Interior.ColorIndex = 3
            End With
        End With
        Next i
    Application.ReferenceStyle = xlA1

 

[knigget]

I'm about to sign off for the night but is this the summary?

F: silver if it is "N/A"
G: silver if F is "N/A otherwise red if G contains a date < today

H: silver if it is "N/A"
I: silver if H is "N/A otherwise red if I contains a date < today

J: silver if it is "N/A"
K: silver if J is "N/A otherwise red if K contains a date < today

etc across the columns?

Correct and

=AND(G3< TODAY(),G3>0)[/QUOTE]

thank you for this.

The code that AlphaFrog works perfectly incase this has been keeping you up at night!

Thank you both - much appreciated