Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Conditional Formatting (Office 97 SR2)

    Hi

    I am trying to shade a row with Conditional Formatting based on the Highest number in Column G, but I just can't get the formula right!

    any help gratefully received.

    Braddy

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formatting (Office 97 SR2)

    Hi,
    If I understand you correctly you want
    Formula Is =max(G:G)=n
    where n is the number you want to test against.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi Rory

    Nick Groves 331,047 331,047
    Walter Scott 407,283 407,283
    Anthony Cowle 1,273,017 1,273,017

    I want to shade the whole row which in this case would be anthony cowle because his figures are the highest.

    Hope I am clear about this
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting (Office 97 SR2)

    How about this as the formula (assuming you start in row1)

    =$G1=MAX($G:$G)

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    hi sdckapr

    I can't get that to work it gives me no formatting at unless I'm doing it wrong!!
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Office 97 SR2)

    The expression probably needs parentheses:

    =($G1 = MAX($G:$G))

    See attached workbook
    Attached Files Attached Files

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formatting (Office 97 SR2)

    Hi Braddy,
    Steve's suggestion should work for you - you just need to make sure that you replace the 1 with whatever row your activecell is in when you apply the format - e.g. if you highlight rows 2 to 20, your active cell should be in row 2 so your formula is =$G2=max($G:$G). I've attached a sample of this so you can see it does work!
    Hope that helps
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi again

    if I use "=IF(G4>5500,TRUE,FALSE) It will shade the whole row if the criteria is met however I want to change the criteria from > to max for column G:"
    If you are a fool at forty, you will always be a fool

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi Rory

    Thanks to you and Hans for your replies I think my problem is Column G is a result of a formula.

    Duh

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formatting (Office 97 SR2)

    Hi Braddy,
    That shouldn't make a difference. Can you post a sample of your workbook?
    (Based on your last post to Steve, you just need =$G4>max($G:$G) if that helps.)
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Office 97 SR2)

    I don't think that formulas in column G should pose a problem, unless they cause circular references or something like that. See modified attached file.
    Attached Files Attached Files

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi Hans

    Thanks everyone for your replies I can see that it does work, there must be an anomoly with my spreadsheet.

    Thank you for your time

    Braddy
    If you are a fool at forty, you will always be a fool

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting (Office 97 SR2)

    This is the same as:
    =G4>5500
    It will highlight different rows depending on what the activecell is when you set it, Since the "G" and the 4 is relative!
    =$G4>5500 will "lock on col G and the row will be relative

    Steve

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi

    Steve thanks for your reply, As I said in a previous reply I think there is an anomaly with my spreadsheet as I cannot get any of the suggestions to work

    Braddy

    <img src=/S/nurse.gif border=0 alt=nurse width=19 height=20>
    If you are a fool at forty, you will always be a fool

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting (Office 97 SR2)

    could you post it, it might be something simple?

    Some mistakes I have seen:
    1) forgetting to indicate a format for a condition
    2) not realizing that Cond formats do NOT combine
    If Case1 is true, it does NOT matter what Case2 and 3 are, they are ignored
    If case1 is false, and case 2 is true it does NOT matter what case 3 is
    For case3 formatting to be picked, case1 and case2 must bothe be false

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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