I have 5 cells in a row formatted as percentages (A1 to A5). The cells are initially 'Blank', however I want all five cells to be highlighted in a colur when they are empty, which is easy enough, but then when the user enters percentages into these five cells, I want them to remain highlighted whilst the Sum of the five cells is less than 100%, but as soon as the total = 100%, irrespective of how many cells are completed (e.g., the user could enter 100% in the first cell), I want all the cells to lose their conditionally formatted highlights...
So, before the user enters anything, cells A1 to A5 are conditionally formatted to have a yellow fill. If the user enters 20% in A1 and 80% in A2, I need cells A1 to A5 to no longer be highlighted in yellow because the total of A1 to A5 = 100%, likewise if the user enters 20% in A1, 20% in A2, 30% in A3, 10% in A4 and 20% in A5, then only when the entry is made in A5, should the cells A1 to A5 no longer be highlighted. The objective is to ensure that the user makes sure that the figures they enter in cells A1 to A5 always total 100%. If they enter figures in cells A1 to A5 and the total is less than or more than 100%, then I need a warning message (which I can do using Data Validation) to warn them that their entries don't total 100%.
I've been trying to figure this out for ages and have so far failed to find a solution that allows both these scenarios to work together. Can anyone provide any solutions to this problem.
Many thanks
Bookmarks