Copy formatting, Help

[robert morris]

I have the following Conditional Formatting Formula (which works for the one
Row); =D11=MAX($D$11,$F$11,$H$11,$J$11,$L$11,$N$11)

Problem is I cannot copy this row down as needed. Excel always converts the
Relative references to Absolute. I have used F4 to toggle to Relative but
soon as I "Apply" they convert back to Absolute.

What am I missing? Also, I'm using Office 2007.

Bob

 

[T. Valko]

It works for me.

Are you sure that's the correct formula you want to use?

I'm thinking you probably want to use this:

=D11=MAX(D11,F11,H11,J11,L11,N11)

Let's assume your data is from row 11 to row 20.

Select the range D1120

Goto Home tab>Styles>Conditional Formatting>New Rule>Use a formula to....

Enter this formula:

=D11=MAX(D11,F11,H11,J11,L11,N11)

Click the Format button
Select the Fill tab
Select the desired color
OK out

 

[robert morris]

Biff,

Nothing works. Excel always changes the Relative Cells to Absolute. I've
tried everything. The range of D1120 changes to $D$11:$D$20 and colors
every row. I also tried Range of D11,F11,H11,J11,L11,N11. Same problem,
all cells color until one cell is selected in a Row. That Row then works
correctly one time through, then the second test run on that Row leaves all
cells colored. I'm lost! Any more ideas?

Bob

 

[T. Valko]

Can I see the file?

If you want to send a copy to me I'll take a look. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Let me know if you are going to send the file. I don't always check that
email account.

 

[robert morris]

Biff,

I just emailed it.

Bob

Can I see the file?

If you want to send a copy to me I'll take a look. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Let me know if you are going to send the file. I don't always check that
email account.

 

[T. Valko]

Ok, got the file.

I'm not real sure what you're wanting to do here.

Highlight the highest pos in each row?

 

[robert morris]

Biff,

Yes, highlight the highest score in each row. Notice the highest score in
each row is not added in the Total (O11) score.

Bob

P.S. Where are you located. I'm up all night because I'm old.

 

[T. Valko]

Ok, since the file is in xls format I did this in Excel 2002.

Based on the sample file you sent...

Select cell D11
Goto Format>Conditional Formatting
Formula Is:

=D11=MAX($D11,$F11,$H11,$J11,$L11,$N11)

You already have the format style set so just OK out.

Now, with D11 still selected...
Goto Edit>Copy
Press and *hold down* the CTRL key then select D1216, F11:F16, H11:H16,
J11:J16, L11:L16, N11:N16
Release the CTRL key
Goto Edit>Paste Special>Formats>OK

The same technique will work in Excel 2007 (just the menu locations are
different)

P.S. Where are you located.

Pittsburgh, Pennsylvania, USA

Go Steelers!
RIP Myron Cope

 

[robert morris]

Biff,

OK, tell me you had that right off the top of your head! Works like it
should but, why does the "fill" color fill the row until a cell is selected?
This is not a problem, just curiousity. Without the fill color nothing
appears until I enter a number in a cell then the high number goes to the red
color as it should.

Thanks a bunch (big bunch) for the help.

I'm a trans-planted Sooner from Oklahoma now living on Hilton Head Island,
SC and a die-hard Dallas Cowboy fan. We had some great games with the
Steelers many years.

Go Cowboys

Thanks again,

Bob

 

[T. Valko]

Not sure I follow you on this. There is no fill color set in the condtional
formatting. It's set to make the TEXT red and nothing more.

OK, tell me you had that right off the top of your head!

Pretty much. I do this kind of stuff all day, every day! I "better" know
what I'm doin'.