Excel - Conditional Formatting - Fragmentation issues.

Question

I have conditional formatting for a range of cells using a formula to provide the format:
Formula: =$A4=Misc.!$C$11 (The referred tab is "Misc")
Range: =$A$4:$F$1048576

The conditional formatting shades a row based upon the data in column A. There are 19 of these formulas with different colors, the above is an example of one such formula.

As I use the spreadsheet, for updates, I will cut the cells (A3:F#) and move them to a completed area below their original location (e.g. row 35…). Still within the same column positions, just in a higher row number.

The problem is that every time I relocate some cells, the conditional formatting becomes fragmented. The rules are the same, however the original range becomes fragmented with many duplicates of the same rule set, with different ranges.

The problem with this is that after a short time, the conditional formatting rules become so large the formatting no longer works. There seems to be no easy way to correct this as the Conditional Formatting editing window is very clunky, and the duplicated rules need to be deleted one at a time. With 19 rules, after a few duplications, the list gets very long.

How do I keep the Conditional Formatting from becoming fragmented and duplicating these rules?

Answer 1

I solved the issue - Apparently the spreadsheet was corrupted.

I copied the data to a new spreadsheet, re-created the conditional formatting, and now it works properly.

It is curious to me why there is no repair feature for large spreadsheets. It took a long time to re-create the new spreadsheet.

Answer 2

Could you share a test file to reproduce your issue?

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.  *Please make sure you have removed any sensitive or private information in the sample file before uploading.

Answer 3

I tried that, however it had the same result.

The current procedure is to cut the row data (e.g. cell range A-F), then insert the range below, moving all older data down.

I tried the following:

1. Insert row at target area,
2. Copy row data (e.g. cell range A-F),
3. Paste row data (e.g. cell range A-F) in inserted rows,
4. Go back and delete previous cells

The overall effect is the same. It does not seem to matter if I cut/insert cells or the entire row.

Note: all of the cut/paste/copy activity is all entirely within the Conditionally Formatted area.

Answer 4

You may try copying and paste value only to new range. Then delete the previous cell range.