Help with retaining conditional formatting in new rows..

[sterjohn]

Hoping someone can help..
I have a spreadsheet with conditional formatting that fills a row with colour when text is inserted in column G.
Is there a way that I can insert new rows and have the conditional formatting apply to the new rows?
Any advice would be much appreciated..
JS

 

[Joe4]

If you insert your rows within the Conditional Formatted range, the Conditional Format will automatically apply to the new row without you having to do anything.
So I am guessing that you are either inserting rows just above or below where the Conditional Formatting starts/ends.
If you apply the Conditional Formatting to the ENTIRE range, then you won't have any issues, and it should be appllied to every row, even newly inserted ones.

 

[Alex Blakenburg]

So I am guessing that you are either inserting rows just above or below where the Conditional Formatting starts/ends.

When I tried it adding data to the row below still picked up the conditional formatting.
If you use an Excel Table and apply the formatting to the whole column, adding to both the top and the bottom worked.

 

[Joe4]

When I tried it adding data to the row below still picked up the conditional formatting.

Interesting. It appears to be to depend on where you insert it.
If you insert between the last row with Conditional Formatting and first row without it, it does seem to pick it up.
But if you insert down any further, it will not.

I guess we would need more details on the situation, i.e.
- What is the range the current CF is applied to
- What is the actual CF rule
- Where they are inserting the new rows

 

[sterjohn]

If you insert your rows within the Conditional Formatted range, the Conditional Format will automatically apply to the new row without you having to do anything.
So I am guessing that you are either inserting rows just above or below where the Conditional Formatting starts/ends.
If you apply the Conditional Formatting to the ENTIRE range, then you won't have any issues, and it should be appllied to every row, even newly inserted ones.

Hi Joe4,
thanks for your reply..
I keep having the problem where I have selected the entire worksheet to apply conditional formatting to and it works fine for a while but then for no apparent reason
it stops working properly.
I was wondering if there is a way to "lock" the formatting so it cannot change?
Thanks again,
John.S

 

[Joe4]

Hi Joe4,
thanks for your reply..
I keep having the problem where I have selected the entire worksheet to apply conditional formatting to and it works fine for a while but then for no apparent reason
it stops working properly.
I was wondering if there is a way to "lock" the formatting so it cannot change?
Thanks again,
John.S

When "it stops" working properly, do some investigation on the Conditional Formatting. If you inspect the Conditional Formatting rules, what ranges are they being applied to?

If it is somehow being removed, I suspect that you have something else (maybe some other VBA code) interfering with things, especially if you had applied Conditional Formatting to the entire sheet.

Also note.
How exactly are you inserting the new row?
Are you then copying anything from elsewhere into this new row?
Note that you may be copying the formatting from the other range, which would then overwrite the formatting in this range when you paste it.

 

[Alex Blakenburg]

Can you also go into Conditional formatting > Manage Ruless > select "This Worksheet and show a screenshot of what it looks like ?