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]

Why not just get the macro to add the Conditional Formatting to F3:F50 each time as well as whatever else it is doing?

 

[AlphaFrog]

As Peter_SSs suggests, add something like this to your macro to re-apply the CF to the cells. This applies the CF to D3:D50. Change it to suit.

    With Range("[COLOR="Red"]D3:D50[/COLOR]")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=F3:F50=""N/A"""
        .FormatConditions(1).Interior.ColorIndex = 15
    End With

 

[Peter_SSs]

As Peter_SSs suggests, add something like this to your macro to re-apply the CF to the cells. This applies the CF to D3:D50. Change it to suit.

    With Range("[COLOR="Red"]D3:D50[/COLOR]")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=F3:F50=""N/A"""
        .FormatConditions(1).Interior.ColorIndex = 15
    End With

I'm not even really sure why that works, but why not just?

Formula1:="=F3=""N/A"""

 

[knigget]

Thanks guys,

What if I wanted to apply this to all cells from A3 to AO50?

 

[AlphaFrog]

    With Range("A3:AO50")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$F3=""N/A"""
        .FormatConditions(1).Interior.ColorIndex = 15
    End With

 

[Peter_SSs]

Thanks guys,

What if I wanted to apply this to all cells from A3 to AO50?

Change the range in AlphaFrog's code to A3:AO50
Put a $ in front of the F in the formula
Formula1:="=$F3=""N/A"""

 

[knigget]

Forgive me for I am a newb..if we are now using A3:AO50, why are we still referencing F3?

Perhaps if I explain...
My spreadsheet CF starts life like this:

F3:F50:
Condition 1

=F3="N/A" silver

Range F3:F50 is copy and pasted to H3, J3, L3, N3 all the way acrossto AN3.

G3:G50:
Condition 1

=SEARCH("N/A",F3) silver

Condition 2

=G3="" no format set

Condition 3

=G3=something else but it won't work on this forum! red

<TODAY()+0[ p red< CODE]>Range G3:G50 is copy and pasted to I3, K3, M3, O3 all the way across to AO3.

That is what I want to do but using a macro that you guys suggested.. can you help?

To see my sheet in action:
http://www.mediafire.com/?qtpw0aqs6ql3tdq - Password = 'Password1'

Thanks in advance

 

[Peter_SSs]

Forgive me for I am a newb..if we are now using A3:AO50, why are we still referencing F3?

Because, your original question (before you edited it - I've only just noticed that it was edited) I think was to format D3:D50 if column L was "N/A" and you subsequently asked how to apply the CF to all those columns. To me, that meant to format them all silver if column L was "N/A".

Now, in relation to the rest of the information ..

Firstly, I choose not to download files from other sites so I haven't seen your workbook. You will get many more potential helpers if you can post small screen shots directly here in the forum. My signature block has 3 suggestions for how to do that.

1. Why do you have a Conditional Format condition that doesn't apply any format?

2. What is that condition 3? If it has a < sign in it, put a space immediately after the < sign.

3. Can you confirm that condition 1 in columns G, H, J, ... etc all look at column F to check for "N/A"?

 

[knigget]

Hi Peter

1. Why do you have a Conditional Format condition that doesn't apply any format?

Because of condition 3 - if I don't apply a 'no format set' rule then it turns the whole column red.

2. What is that condition 3? If it has a < sign in it, put a space immediately after the < sign.

=G3< TODAY()+0

3. Can you confirm that condition 1 in columns G, H, J, ... etc all look at column F to check for "N/A"?

Condition 1 does not look at column F. It looks at itself, if it says 'N/A' then it turns itself silver - it is individual and not related to the content of any other cell.


The screenshot you requested:

And the explanation:

If a cell has n/a - turn itself silver

=F3="N/A" silver

AND then one next to it

=SEARCH("N/A",F3) silver

If the cell has nothing in it, do nothing

=G3="" no format set

If the cell has a date which is earlier than today turn it red

=G3< TODAY()+0

HTH