Conditional formatting rule accumulation after deleting row(s)

[dgiberga]

Hello,

I have researched my issue quite a bit on various forums and blogs, but I am unable to find a solution which specifically addresses the problem I'm having.

Here is a link to another thread in this same forum with a similar issue (I replied to the thread with a link to a solution to that problem).

The problem I'm having:

I have Conditional Formatting (CF) rules applied to a table on a worksheet. The CF rules in my table get split (the ranges the formula is applied to splits into new rules) each time I delete a row. The result is that, every time I delete a row in the table, a new split in the range occurs, generating yet more and more rules (again, see the thread I have linked to and click on the link I created there).

Example:

I have created CF rules to color-fill cells in certain columns based on the conditions of relative cells in the same row (i.e. $B3:$B79 should turn yellow based on the condition of adjacent cells in column $G3:$G:79). The formula used evaluate the adjacent cell(s) is:

Please Login or Register  to view this content.

.

Now, if I were to delete, say, row 59 entirely, I now see the CF rule duplicated, but it contains a new range (=$B$4:$B$60), while the range for the original rule has also been modified (=$B$3,$B$61:$B$78). It seems that Excel is trying to adapt the CF rule to the table's new range, given the deleted row.

This wouldn't be a problem, except; If I now go to add a new record to the last row of the table, Excel "knows" to include the new record in the table, but Excel does not expand the range of the CF Rule to include the new record (in other words, the CF is not applied to the new record).

I've tried a couple of possible work-arounds, to no avail:

-Clear contents (instead of delete row) and then re-sort the table to push the record to the end, then delete the empty row.
Result: multiple CF rules

-Clear contents (instead of delete row) and then re-sort the table to push the record to the end, then resize table.
Result: Conditional formatting is applied to rows outside the table. Deleting the outside row results in multiple CF rules.

The strange thing is, I have other CF rules which do not split ranges when records are deleted. The formula is slightly different:

Please Login or Register  to view this content.

Which is supposed to change color of text to Blue when the text "DrugCt" is present in a cell within the range.

So, I tried re-coding the formula for my problem CF rule as follows, hoping it was the solution:

Please Login or Register  to view this content.

, then deleted a row.
Result: multiple CF rules.

I know this was a long post, but I wanted to give as much detailed information as possible. I am stuck having to reselect the ranges for the 2 problematic CF rules every time I delete a row. Very frustrating.

I appreciate any help or insight!

[TMS]

Superb explanation of the problem ... maybe a little overwhelming if you've never encountered the problem. But I have

I'm not 100% sure why it happens, but I know it surely does. And it can be hugely problematic. In my scenario, I was replicating and deleting large blocks of data which could be a number of columns or a number of rows, each block having several CF conditions. In testing, it worked fine. In real life, the workbook ground to a halt and the Name Manager wasn't even capable of listing all the conditions.

I eventually opted to use a macro called in the Workbook Open event to delete and re-create the CF formulas and formatting. Bit of a cludge but it is executed every time the workbook is opened so it never gets out of hand.

Regards, TMS