Results 1 to 7 of 7

Inserting/Deleting Rows Causes Issues with Conditional Formatting Macro

Threaded View

  1. #1
    Registered User
    Join Date
    09-29-2014
    Location
    Dallas, TX
    MS-Off Ver
    2010
    Posts
    45

    Inserting/Deleting Rows Causes Issues with Conditional Formatting Macro

    I have a macro that does a bunch of conditional formatting {i.e. changes the fort color based on date (items older than today are red, today plus the next 7 days are purple) and shades other items mint green if column G has 1 or 3 or H has a 1 - this probably TMI)}. See below. There's also a sample attached.

    The macro works fine. It does exactly what it should. Here's the problem. If I insert or deleted a row, the conditional formatting rule breaks/splits. The best way to describe it is.....the formatting is stuck in/with the cell despite the fact that the data (which is what caused the cell's font/fill to change in the 1st place, but that's the point of a conditional) has been shifted. Any advice on how to fix it?

    Sub TicklerConditionals()
    '
    ' TicklerConditionals Macro
    '
    
    '
        Cells.FormatConditions.Delete
        
        Columns("A:M").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=COUNTIF($G1,""3"")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.599963377788629
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Columns("A:M").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=COUNTIF($H1,""0"")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.599963377788629
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Columns("A:M").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=COUNTIF($G1,""1"")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.599963377788629
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(MEDIAN(TODAY(),$I1,TODAY()-1065)=$I1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16776961
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(MEDIAN(TODAY()+1,$I1,TODAY()+7)=$I1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -6279056
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    
    End Sub
    Attached Files Attached Files
    Last edited by lashellr; 11-05-2014 at 11:29 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting Behavior When Deleting Rows
    By DanBlum in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-08-2014, 01:08 AM
  2. Conditional formatting splits when inserting new rows
    By physicsgal in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-16-2013, 09:58 AM
  3. Deleting rows without conditional formatting
    By ShawnaC in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2013, 02:38 PM
  4. Disable macro when inserting/deleting rows
    By aznprod517 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-29-2009, 09:40 AM
  5. [SOLVED] Conditional Formatting Reference / Inserting Rows
    By Werner Rohrmoser in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2005, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1