Excel 2007 : Conditional Formatting Problem

1. Conditional Formatting Problem

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

2. Re: Conditional Formatting Problem

Color the cells yellow as standard formatting (not conditional)
Somewhere on your sheet hide the following formula: =IF(SUM(A1:A5)=1,TRUE,FALSE)
Select your five cells, go to conditional formatting, start a new rule, and select "Use a formula to determine which cells to format"
In the Edit the Rule Description window, refer to the cell that contains the formula in step 2 above
Click the format button, go to fill tab, choose "No Color" under the Background Color selection and click OK.

3. Re: Conditional Formatting Problem

Hi jch.2

Okay, I tried that, but it doesn't remove the 'colour' formatting on the cells once the SUM of cells A1 to A5 = 100%. I've attached a sample, am I missing something?

Many thanks

4. Re: Conditional Formatting Problem

Okay, skip that last message, I've just figured out why it wasn't working and now it is, so many many thanks, that works a treat...

Thank you...

5. Re: Conditional Formatting Problem

HI

In Conditional Formatting rules, formula is>>=SUM(\$A\$A1:\$E\$10)=100% >cHOOSE COLOR>>ok.

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

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