Forum Discussion
Solved
How do i highlight a particular cell/change color when a particular condition is met.
Hi, i am trying to create a matrix to manage score and calculate skins for golf tournament, where i want to highlight a cell automatically when a certain condition is met (only one player with low score). In this case, i want to have the the players cell highlighted when player gets the lowest score and he is the only one with the lowest score. fror example in matrix below, player 1 was the only one to get low score of 1 so his cell got highlighted. i tried to do conditional formating to highlight low score but it works fine with one player has low score, but as soon 2 or more players have low score their cells also get highlighted. i want to add a condition that when there is only player with low score then only low score gets highlighted.
Is there a way to do this calculation
ThaNKS
Sukhbir
Okay, let do it bit another way and step by step. I based on my test sheet:
in row 12:12 we have lowest score for all players, and in 13:13 number of such players.
Apply to the cell $B$1 conditional formatting rule
=(B1=B$12)*(B$13=1)
which check if simultaneously B1 has minimum score and we have only one such player in B:B column. Be careful with absolute and relative references in formula, that's important.
After that pick-up format of the cell B1 by Format Painter and apply it sequently to cells B3, B5, B7, B9.
You may stay on cell B9 and check Conditional formatting for it, it shall show the rule
=(B9=B$12)*(B$13=1)
After that select entire column B:B (or only cells B1:B9), pickup its format by Format Painter and apply it to next columns C:C, etc.
For cell C9, for example, it shall appears the rule
=(C9=C$12)*(C$13=1)
Now you have lot of rules for you matrix, one separate rule for each cell with the score. In my previous post that was one rule applied to set of cells. Result is the same.
If your matrix is as small as in your example and numeber of players is more or less fixed above is enough, for big matrix and flexible number of players some optimization could be applied.
Hi Sukhbir,
The simpliest solution looks like this
but that's only to illustrate the idea
Thanks Sergei, i tried your solution but i am not getting the required results. Maybe i am not putting the formula in correct order.
Thanks though.
Sukhbir
Okay, let do it bit another way and step by step. I based on my test sheet:
in row 12:12 we have lowest score for all players, and in 13:13 number of such players.
Apply to the cell $B$1 conditional formatting rule
=(B1=B$12)*(B$13=1)
which check if simultaneously B1 has minimum score and we have only one such player in B:B column. Be careful with absolute and relative references in formula, that's important.
After that pick-up format of the cell B1 by Format Painter and apply it sequently to cells B3, B5, B7, B9.
You may stay on cell B9 and check Conditional formatting for it, it shall show the rule
=(B9=B$12)*(B$13=1)
After that select entire column B:B (or only cells B1:B9), pickup its format by Format Painter and apply it to next columns C:C, etc.
For cell C9, for example, it shall appears the rule
=(C9=C$12)*(C$13=1)
Now you have lot of rules for you matrix, one separate rule for each cell with the score. In my previous post that was one rule applied to set of cells. Result is the same.
If your matrix is as small as in your example and numeber of players is more or less fixed above is enough, for big matrix and flexible number of players some optimization could be applied.
