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)
Bookmarks