Results 1 to 5 of 5

lose range conditional formatting adding/deleting rows

Threaded View

  1. #1
    Registered User
    Join Date
    05-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    lose range conditional formatting adding/deleting rows

    I have 2 conditional formatting formulas/rules for 2 ranges of cells which are adjacent to each other and when I add or delete rows to the rages I often lose the conditional formatting.

    I have a conditional format to highlight the lowest golf scores for a set of golfers (i.e. skins game). There's a separate skins game and pot for "A" players and "B" players. SO to highlight the "A" skins I have a conditional formatting rule from [E2:M6] to highlight the "A" skins (Mary's 3 on hole#5 & Rick's 3 on Hole#1)....and another rule for the "B" skins from [E7:M11] (Jill's 3s on hole#3 & hole#5):


    row1- Date | Name | Flight | Team | hole_1 | hole_2| hole_3| hole_4 | hole_5 | hole_6| hole_7 | hole_8 | hole_9 | Score
    row2- 5/29 | Matt | A | #1 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 36
    row3- 5/29 | John | A | #2 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 36
    row4- 5/29 | Mary | A | #3 | 4 | 4 | 4 | 4 | 3* | 4 | 4 | 4 | 4 | 35*
    row5- 5/29 | Tom | A | #4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 36
    row6- 5/29 | Rick | A | #5 | 3* | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 35*
    row7- 5/29 | Joe | B | #1 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 36
    row8- 5/29 | Bill | B | #2 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 36
    row9- 5/29 | Jill | B | #3 | 4 | 4 | 3* | 4 | 3* | 4 | 4 | 4 | 4 | 34*
    row10-5/29 | Mark |B | #4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 36
    row11-5/29 | Suzy | B | #5 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 36



    The problem is keeping the skin highlights when I delete and add new golfers for the next round. Especially when I delete rows at the beginning and end of each formatting range (row2, row5 or row 7 between As & Bs and row 11). The number of golfers vary from week to week so this is a dynamic list....and players can switch from an A to a B or vis versa any given week.

    Here's the boolean equation I use to calculate the TRUE/FALSE for highlighting (i.e skin) , but I don't believe it's not relevant to the thread.

    =AND(E2=MIN(E$2:E$6),COUNTIF(E$2:E$6,MIN(E$2:E$6))=1)
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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