Conditional formatting based on other cell for entire column

[kpierce]

Hello -
I am wanting to format (colr fill with Blue), columns A,B,C if cell D is equal to "Your Company"

I have researched and have it work when I select:

Select $A$1:$C$1
Conditional Formatting-Use Formula to determine which fields to format
Then enter in the formula:
= $D$1 = "Your Company"

However, I want to apply this rule to the entire column, because I have a macro that comes in and deletes all the rows before starting so this also takes the conditional formatting with it.

I am trying to get something like the following to work, but am have very little luck.
Highlight columns A,B,C
So I get
Applies to =$A:$C
Formula =$D:$D = "Your Company"

But this does not work.

If I do the following it works:
Applies to =$A:$C
Formula =$D$1 = "Your Company"

But if I then delete row 1 in my macro I loose the conditional formatting.

Is there a way around this?

as always, thanks in advance.

 

[wigi]

Why does the macro delete rows, or clear rows? You should just remove the contents of the row(s) by using .ClearContents instead of .Delete.

 

[kpierce]

Great....

I did not realize I could do that...

Thanks.