Feedback by UserVoice

Darryl Pinto

My feedback

  1. 72 votes
    Sign in
    (thinking…)
    Sign in with: facebook google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Darryl Pinto commented  · 

    I have seen and worked with this headache. However, I have noticed that while my coworkers experience this conditional formatting split, I do not. I am not sure why. I am running Windows 10, Excel 2016 64-bit version 16.0.4738.1000. Did this get fixed in Excel? Is there a Windows update for this? I do not know.

    But since my coworkers still experience this problem, I have created a solution.

    I agree with what has been posted previously; copy and paste values (or paste formulas) will totally work to not split up the conditional formatting.

    I'm a little lazy for that. And I don't want those who use my spreadsheets to have to do that. I'm also not entirely confident that they will remember to do copy and paste values. :(

    I don't know if this solution will work for your needs, but I resorted to deleting all conditional formatting and reapplying the correct conditional formatting every time the workbook is opened.

    Because this macro runs every time the workbook is opened, the user does not need to change the way they copy and paste. They don't need to know that the macro is even there. They don't need to manually run the macro; it is automatic. I feel this creates a better user experience.

    Please keep in mind that this code needs to be copied and pasted into the "This Workbook" module; not a regular module.

    Private Sub Workbook_Open()
    'This will delete all conditional formatting and reapply the conditional formatting properly.
    'After copying and pasting the conditional formatting get split into two or more conditional formattings. After a few
    'weeks there are so many conditional formattings that Excel crashes and has to recover.

    Dim ws As Worksheet, starting_ws As Worksheet

    Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "InvErr" Then
    ws.Activate
    Cells.FormatConditions.Delete
    ''Every Other Row Tan
    Range("A4:M203").FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(ROW(A4))"
    Range("A4:M203").FormatConditions(Range("A4:M203").FormatConditions.Count).SetFirstPriority
    Range("A4:M203").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    Range("A4:M203").FormatConditions(1).Interior.ThemeColor = xlThemeColorDark2
    Range("A4:M203").FormatConditions(1).Interior.TintAndShade = 0
    Range("A4:M203").FormatConditions(1).StopIfTrue = False

    ''Highlight Duplicates Red
    Columns("B").FormatConditions.AddUniqueValues
    Columns("B").FormatConditions(Columns("B").FormatConditions.Count).SetFirstPriority
    Columns("B").FormatConditions(1).DupeUnique = xlDuplicate
    Columns("B").FormatConditions(1).Font.Color = -16383844
    Columns("B").FormatConditions(1).Font.TintAndShade = 0
    Columns("B").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    Columns("B").FormatConditions(1).Interior.Color = 13551615
    Columns("B").FormatConditions(1).Interior.TintAndShade = 0
    Columns("B").FormatConditions(1).StopIfTrue = False
    End If
    Next

    starting_ws.Activate 'activate the worksheet that was originally active
    Application.ScreenUpdating = True

    End Sub

Feedback and Knowledge Base