Results 1 to 8 of 8
  • Thread Tools
  1. 3 Star Lounger
    Join Date
    Nov 2001
    Posts
    213
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional formatting (Excel 2000)

    Hi

    I am looking for advice to do some conditional formating

    I have a sheet which contains the following information
    Persons Name
    Week 1 hours
    Week 2 hours
    Week 3 hours
    Week 4 hours
    Total of hours weeks 1 to 4
    Week 5 hours
    Week 6 hours
    Week 7 hours
    Week 8 hours
    Total of hours weeks 5 to 8

    I would like to colour the name Red either of the 2 totals are greater than 100%, Yellow if either of the totals is between 80% and 100%, finally Green if both are <80%. The % is calculated based on total hours over 160 hours per week. I would like to know if it can be done without writing and macro.

    Thanks for any pointers

    Mike

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2000)

    You want to use Format | Conditional Formating with three conditions (this example is for cell B1:
    Condition 1, Formula Is =OR(B6>160,B11>160), Format is Red Font
    Condition 2, Formula Is =OR(B6>=128,B11>=128), Format Is Yellow Font
    Condition 2, Formula Is TRUE, Format Is Green Font

    Then you can use the Format Painter to copy the format to the other names. This is confusing the first couple of times. I've attached a sample workbook. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. 3 Star Lounger
    Join Date
    Nov 2001
    Posts
    213
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (Excel 2000)

    Thanks Sam,

    I will try this first thing in the morning.

    Regards

    Mike

  5. 3 Star Lounger
    Join Date
    Nov 2001
    Posts
    213
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (Excel 2000)

    Thanks Sam,

    That worked fine if I use fixed values, the only problem I had was if I tried to use a calulation eg =OR(((B6/$H$1)*100)>100,((B11/$H$1)*100)>100), I always got the resul of the first condition.

    Regards

    Mike

  6. Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2000)

    What's in H1?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. 3 Star Lounger
    Join Date
    Nov 2001
    Posts
    213
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (Excel 2000)

    Hi Sam,

    H1 would contain the number of hours per month and can vary depending on the number of weeks in a month. So I would use H1 for calculations instead of puting a fixed value in the calculation.


    Regards

    Mike

  8. Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2000)

    Your formula looks OK, but I would use the following instead for clarity: (Also see attachment)
    <pre>=OR(B6/$H$1>100%,B11/$H$1>100%)
    =OR(B6/$H$1>=80%,B11/$H$1>=80%)
    TRUE</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. 3 Star Lounger
    Join Date
    Nov 2001
    Posts
    213
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (Excel 2000)

    Thanks Sam......................... Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •