Proper way to apply conditional formatting to new entries of data.

[Melvinrobb]

Hi all
My question is more a general issue i've been having.
for a group of 200 entries I have applied conditional formatting.
The issue I'm having is that I am continually adding new entries to this list, and simply copy and pasting the formats does not work well.

If I have the following listed in my conditional formatting: =IF($E4=$F$2,TRUE,FALSE)
applied to cells =$D$4:$F$271
and I add 5 new entries, and copy and paste the formats from an old row to the new row, I do not want to add new conditional formatting
I simply want the current conditional formatting to now apply to cells =$d$4:$f$276
The way i am currently copy and pasting formatting for new entries I can go from 1 conditional format to 15 in a matter of days, as new conditional formatting parameters are added with each new entry.

If i am not being clear, i apologize...
Thanks for any assistance!

[BenMiller]

You can change your IF statement to a simpler =$E4=$F$2

If you add an AND to ensure it does not equal zero, you can add the conditional formatting to the entire column, and new entries would automatically be included. So: =(E4=$F$2)*(E4<>0)

[Melvinrobb]

I know the IF statement is unnecessary. Force of habit I guess. I also can copy and past the formula and change the true and false with other values quickly if needed.
Another conditional format in another table I use is for the bottom 50 values in column B be highlighted green, in column C be highlighted blue, etc. I don't think your solution would work in that instance.
Is there a way to copy and paste everything BUT conditional formatting? If there was, I could do that, and then manage my conditional formats to now include the extra rows.

I just realized right before I posted i could copy and paste formats, and then remove conditional formats on selected sections. Then manually go through and change the cells from $f$271 to $f$276.
Is there a better way?

[BenMiller]

You can copy then right-click>paste special>formulas to copy everything but the formats...

[Melvinrobb]

I want the formats to paste (Font, Borders, etc.), but exclude the CONDITIONAL formatting.
In reality I do want to include conditional formatting, but it will just get cluttered if I have the conditional formatting paste on every new entry.

[BenMiller]

I believe that would be impossible. Conditional formats are considered the same as "regular" formats, at least when it comes to copy/paste. So you can do a regular copy/paste, then highlight the column and delete the CF - as you said.

[Pepe Le Mokko]

We will call the Table of 2010 to the rescue.

I suppose you have applied your CF rules to the range $D$4:$F$271

Select this range - Hit the Insert Tab - Select Table and answer whatever needs to be - Now, when you add rows, the CF will automatically follow as should formulas - Simple as that