+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Conditional Formatting Problem

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    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. #2
    Registered User
    Join Date
    02-23-2011
    Location
    Inactive Profile
    MS-Off Ver
    Inactive Profile
    Posts
    11

    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. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    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
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    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. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting Problem

    HI

    Highlight your range(A1:E1)

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

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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