# Thread: Conditional Formatting (Office 97 SR2)

1. ## 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!

<img src=/S/doh.gif border=0 alt=doh width=15 height=15>

2. ## 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.

3. ## 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.

4. ## Re: Conditional Formatting (Office 97 SR2)

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

Steve

5. ## 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!!

6. ## Re: Conditional Formatting (Office 97 SR2)

The expression probably needs parentheses:

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

See attached workbook

7. ## Re: Conditional Formatting (Office 97 SR2)

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

8. ## 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:"

9. ## 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

10. ## Re: Conditional Formatting (Office 97 SR2)

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.)

11. ## 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.

12. ## 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.

13. ## 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. ## 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

<img src=/S/nurse.gif border=0 alt=nurse width=19 height=20>

15. ## 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 Last

#### Posting Permissions

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