Conditional formatting suggestion – for file amendments inserting columns or rows


I’m sorry if I have to post my suggestion here as I couldn’t find any means to post a screenshot in the website

That in the event of making file amendments, like inserting rows/columns, that the existing conditional formats will adjust automatically in a way that the formula wouldn’t be much distorted, like what it does now whenever I insert a column within a range that is conditionally formatted, and using the command paste (and not further choosing pasting via formulas and number formatting), the system would distort the rules, such that if the conditional format captures a range, and you insert a column in between that range and paste the copied column via insert copied cells or any other simple pasting rules, the conditional formatting rule then would be distorted such that a new rule would be created, and the existing rule would show a distorted ‘Applies to’ range, that it would create a range ending before the inserted column, then a new range to depict the column after the inserted column.

For a clearer picture, this is what I’m trying to say:

Here is the range where the conditional format is applied to, before inserting a column, and copying the previous column and pasting it on the added column via simple pasting (instead of further selecting paste via formulas and number formatting.

And this is the distorted range, which has created a new rule for the inserted cell, and the existing rule where the range would end before the inserted column, and an added range after the inserted column

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

I agree with you completely.

I suggest you try again posting in and include a link back to this post so that people can see the pictures here.  The product team look at the uservoice site but are less likely to look here.

Microsoft Excel MVP, Oxford, England.
Thanks but like I said, I couldn't find the means to post the screenshot.  Maybe you can help me out.
Enjoy the journey, not the destination.

I appreciate that you can't post the screenshot in Excel.uservoice.

That's why I suggested posting the description of the issue there with a link back to this thread so people can see the picture and understand better what the issue is.

An alternative would be to post in Excel.uservoice something like this:

Subject:  Improve merging of conditional formatting


When cells are moved within a conditionally formatted range, the rules are duplicated for the destination cells making management of the conditional formats more difficult.

To illustrate the problem, run this macro which conditionally formats a range of cells, copies column C and inserts before column G.  Then Home > Conditional Formatting > Manage Rules > for This Worksheet.  The rule has been duplicated for the new column F rather than being merged with the original rule which now applies to a 2-area range.  This makes changing the rule for the entire range subsequently more difficult and error prone.

Sub Macro1()
    Workbooks.Add (xlWorksheet)
    Selection.FormulaR1C1 = "=RAND()"
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
End Sub

See also

I could post it on your behalf, and will if you wish, but I think you should take the credit for bringing the issue up.

Microsoft Excel MVP, Oxford, England.
Alright.  Sorry I misunderstood you.
I don't mind if you post it on my behalf.  I guess it has more chances of being implemented if it came from an expert!  Hehe.
Enjoy the journey, not the destination.

Posted at

Please visit that link and vote for the suggestion.

Microsoft Excel MVP, Oxford, England.

Wow!  Unbelievable!  I noticed that it was actually implemented now!

As I had a dummy file and simply made inserts and simple pasting, and I decided to check on the rules of how distorted it would be, and I'm greatly surprised to see that it's not!

Thanks a lot!  I knew this would receive immediate attention as it comes from an expert!

Thanks again!

Well, I hope my other suggestions would gain attention!  Hehe.

Enjoy the journey, not the destination.

I am more than a little surprised.

If you run the macro that I put in the uservoice post do you not still see the issue?

Have you upgraded to Excel 2016 in the meantime? [I didn't test in 2016]

Microsoft Excel MVP, Oxford, England.

Well, I didn’t run the macro and I don’t think it’s necessary to run it up.  I just based it on what I saw upon managing my conditional formats when I was manipulating my draft file.  I tested it again this morning to see if I didn’t make a mistake or forgot to test using the simple ‘insert copied cells’, but it’s really fixed!

Here, let me post screen copies to prove to you that it’s really fixed.

This is the conditional format before I used the ‘insert copied cells’ command.  I note that the formatting for cells<0 captures the entire columns and not just the range I indicated, whereas for cells>0, the ranges are used.

And this is the conditional format after I used the ‘insert copied cells’ button.               

Incredible clout huh?  Hehe.  And no, I still use Office 2013.

This is just great!  Now I don’t have to press a bunch of keys!

Thanks again!

Enjoy the journey, not the destination.

Don't thank me too soon.

Here is what I still see when I run my macro:

I am running 2013 15.0.4787.1002  (32-bit client install).  If you are running the Office 365 version your experience could be different.


Microsoft Excel MVP, Oxford, England.

Well, I don’t know why that’s the case with your macro, and I looked at it again in my conditional formats, and it’s still fixed when I use the simple command ‘insert copied cells’:





I am also running Office 2013.  So that thanking you is still in order, hehe.

Enjoy the journey, not the destination.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.


Discussion Info

Last updated February 22, 2018 Views 31 Applies to: