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.

Please Login or Register  to view this content.

2 problems....

1.You are looking at the MIN for the entire column MIN(O:O) in your formula...i.e all players.... when you should only be looking at the MIN for the scores from just the A players. Recall a "B" player can get a skin against the other Bs without being a MIN value for the entire column. He just needs to be MIN against the other B players.

2. MIN returns the smallest value in the range, but in addition to being the MIN value it also must be UNIQUELY minimum to win a "skin". If everyone gets a 3 on a hole the MIN value will be 3 and return a TRUE when you only want it to be TRUE if the score is = the MIN AND there's only one of them.

Sorry...I didn't mean to imply everyone golfs. I really wish I didn't some days :-)

Anyway I tried your CF formula (both the yellow and blue) and it's not working the same way. Were you able to get this to work? Highlight the smallest unique value in a row (yellow for A, blue for B)?

The real point of the post was how to manage the rage of the CF when adding/deleting golfers not so much how to calculate it. I had that working. The calculation was correct, but the rage of the CF gets confused when players in the range change.