I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless of
month. For example for Phil it would be 98.16, for Mech it would be 96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04

So if the max occurred in the 60 or 90 day column, then nothing would be
highlighted?

If yes...

With the data in A1:E7 (headers in row 1)
I selected E2:E7
and then Format|conditional formatting
formula is:
=e2=max($c2:$e2)

In your test data, wouldn't all those sites get the highlight?


JH wrote:

I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless of
month. For example for Phil it would be 98.16, for Mech it would be 96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04

--

Dave Peterson

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson

"Dave Peterson" wrote in message
...
Ahhh. Now I think I understand the original post.

Ahhh. Now I think *I* understand the original post.

When I copied and pasted the data into my XL somehow I thought that *site
Mon/Yr* was one column. I started to write the post then wend back to check
the worksheet and altered it and that is why I said that the data was in
A2:E7 but my, (altered), formula references only to Column D. The LEFT()
was cutting off the Mon/Yr which I has concatenated into column A. But yes,
now that I see it you are correct.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Peterson" wrote in message
...
Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional
Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site
regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson

Ah, who knows.

But between us, I think we've covered 2 of the many possibilities!

Sandy Mann wrote:

"Dave Peterson" wrote in message
...
Ahhh. Now I think I understand the original post.

Ahhh. Now I think *I* understand the original post.

When I copied and pasted the data into my XL somehow I thought that *site
Mon/Yr* was one column. I started to write the post then wend back to check
the worksheet and altered it and that is why I said that the data was in
A2:E7 but my, (altered), formula references only to Column D. The LEFT()
was cutting off the Mon/Yr which I has concatenated into column A. But yes,
now that I see it you are correct.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Dave Peterson" wrote in message
...
Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional
Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site
regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson

Thanks to you both for your response. Your formula correctly identifies the
top % for each site. Would you also show me how to include a change to the
font color for the top percent for each site, for example the top % for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson

If you're using xl2003 and below and conditional formatting, then you only get 3
formats (plus the normal format).

If you have more than 3 sites and want more than 3 colors, you'd have to use a
different approach--maybe some sort of event macro??

JH wrote:

Thanks to you both for your response. Your formula correctly identifies the
top % for each site. Would you also show me how to include a change to the
font color for the top percent for each site, for example the top % for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson

Thanks Dave.
I only have three sites and was wondering if I could add something to the
code to say if A2 = Phil then the font color should be blue.

"Dave Peterson" wrote:

If you're using xl2003 and below and conditional formatting, then you only get 3
formats (plus the normal format).

If you have more than 3 sites and want more than 3 colors, you'd have to use a
different approach--maybe some sort of event macro??

JH wrote:

Thanks to you both for your response. Your formula correctly identifies the
top % for each site. Would you also show me how to include a change to the
font color for the top percent for each site, for example the top % for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson