+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting based on sum of pairs of cells.

  1. #1
    Registered User
    Join Date
    11-14-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question Conditional Formatting based on sum of pairs of cells.

    Hey, having some trouble with some conditional formatting.

    I have created a table that has working hours of staff members over many weeks. Week number as column headings (1 to 52) and staff name as Row headings. E.g a row may be

    John Smith, 37, 37, 37, 37, 64 (commas to show seperate cells)

    How would I go about using conditional formatting so that the formatting changes according to the sum of the values in each pair of cells?
    I need to add the total hours of every two weeks for some staff and change the fill colour of both cells accordingly to highlight which weeks staff have worked too many/few hours.

    So (B1+C1) would be a pair, the total would decide which fill colour is used on both B1 and C1, and then (D1+E1) would be the next pair and so on.

    I have tried using 'a formula to determine which cells to format' and placing =(B1 + C1) = 74 and making it fill the cells green but this appears to be doing (B1+C1) as the first pair and then (C1+D1) as the second and changing the format for the first cell only.

    I hope I have describe by problem well enough and that someone will be able to help me out.

    If any further clarification is needed just ask and I'll try to explain it more.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Conditional Formatting based on sum of pairs of cells.

    Assuming data actually starts in B2, across and down, select data area and use CF formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-14-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional Formatting based on sum of pairs of cells.

    Is that formula to be applied to the entire table?

    I am looking for the formula just for a single row in the table as different rows(Staff) have different formatting due to people working different shifts.
    I'm looking to apply seperate conditional formatting rules to each row individually within the same table.

    Thanks for the help!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Conditional Formatting based on sum of pairs of cells.

    That rule can be applied to one or more rows starting with row 2 ... and in column B.

    Try it. What can you break?

    Regards, TMS

  5. #5
    Registered User
    Join Date
    11-14-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional Formatting based on sum of pairs of cells.

    I'll try and explain more specifically what I need as I can't seem to get either of the formulas above to work.

    First number starts at B2 and there is a number in each cell in the row from B2 to Z2

    The conditional format needs to work so that B2 & C2 change format as a pair based on the sum of the values within those cells, then D2 & E2 change format as a pair based on the sum of the values within D2 & E2 and continuing all the way till Y2 & Z2.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Conditional Formatting based on sum of pairs of cells.

    That's what it does. Please post a sample workbook so we're both on the same page or this will drag on.

  7. #7
    Registered User
    Join Date
    11-14-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional Formatting based on sum of pairs of cells.

    My apologies, I have it working now, made several little errors that made it appear to not be working.

    Thank you very much for your help!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Conditional Formatting based on sum of pairs of cells.

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  2. Replies: 5
    Last Post: 08-06-2013, 02:13 PM
  3. Replies: 4
    Last Post: 02-25-2013, 06:43 PM
  4. conditional formatting to find absolute value pairs
    By kisboros in forum Excel General
    Replies: 2
    Last Post: 01-04-2013, 02:51 AM
  5. Replies: 4
    Last Post: 01-06-2012, 05:07 PM

Tags for this Thread

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