How can we improve Excel for Windows (Desktop Application)?

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)
Range("A1:H16").Select
Selection.FormulaR1C1 = "=RAND()"
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=0.5"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("C:C").Select
Selection.Copy
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
End Sub

See also http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/conditional-formatting-suggestion-for-file/aba6fe24-70fa-4db1-8819-cd537723b6dd

8 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Bill ManvilleBill Manville shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    3 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base