automatically extending conditional formatting rule to new information

**bite_tony** · 02-22-2013, 07:00 AM

I have set up a spread sheet that other staff members will update at work.
It has conditional formatting rules applied to it, so that as new data is added it colour codes various things.

Do I HAVE to predetermine the catchment area for my conditional formatting?
Is there any way I can set it to be ongoing, so that as a new row of data is added, the formula is extended to this new row?
I ask because I can't be sure how many rows of data will be added month by month, some months it could be two hundred more than other months?

Cheers for your help

**:) Sixthsense :)** · 02-22-2013, 07:12 AM

Whether the formula has been protected to certain range using absolute reference instead of relative? Can you please let us know your cf formula and the cell reference on which it has been applied?

Refer the below link for details about Relative And Absolute References In Formulas
http://www.cpearson.com/excel/relative.aspx

**bite_tony** · 02-22-2013, 07:22 AM

hi, thanks for replying.
I have a few different formulas going, but this is an example =$K4<>""
basically, just that if there is anything in a cell in column K, the whole row for that cell goes red.

**:) Sixthsense :)** · 02-22-2013, 07:28 AM

Ok.. just a quick method for doing this.. Just copy the conditional formatting row and select the new rows for which the CF needs to be applied and press Alt+E+S+F and give Ok, which will add the formatting to the new rows.

**bite_tony** · 02-22-2013, 10:15 AM

Hay.

That did the job, but the problem is, I can't be sure that other people will know to do that when they get to the end of the currently formatted fields..
I know from personal experience that it will ALLLL go wrong

I ended up turning the information to a table within the spread sheet.. this meant that it automatically continued my formatting when new data was added to the row directly below the end of my table.

Cheers for your help though!!