# Thread: Need Help Writing Formula (Excel 2000)

1. ## Need Help Writing Formula (Excel 2000)

I need help writing a conditional formatting formula.

(1) Cells A7 through A36 contain years
(2) Cells B7 through B36 contain numbers
(3) Cells Q7 through Q36 contain numbers
(4) Column R is hidden
(5) Cells R7 through R36 contain formulas that show the difference between the cells in columns Q and B ----- i.e., (=Q7-B7) through (=Q36-B36)
(6) I need to write a conditional formatting formula for cells Q7 through Q36 that will format (with pattern color) the cell in column Q corresponding to the same year that matches MAX(R7:R36)

Robert

2. ## Re: Need Help Writing Formula (Excel 2000)

Would you mind re-phrasing point 6!
<hr>I need to write a conditional formatting formula for cells Q7 through Q36 that will format (with pattern color) the cell in column Q corresponding to the same year that matches MAX(R7:R36)<hr>
You want the Q column to be formatted by pattern if the max(R7:R36) is equal to the year listed in the A column of the same row?

Do I understand correctly?

3. ## Re: Need Help Writing Formula (Excel 2000)

Thanks for your replies, and sorry for the confusion. I had a hard time trying to put point six into words, as you noticed.

For example, if cell R15 (data for year 1988) is equal to MAX(R7:R36), I want cell Q15 (data for year 1988) to be formatted by pattern; if cell R21 (data for year 1994) is equal to MAX(R7:R36), I want cell Q21 (data for year 1994) to be formatted by pattern, etc. Hope this helps to make point six less confusing.

Robert

4. ## Re: Need Help Writing Formula (Excel 2000)

That is what I assumed, so my reply higher up in this thread should do what you want.

5. ## Re: Need Help Writing Formula (Excel 2000)

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> it should be:
=(R7=MAX(\$R\$7:\$R\$36)<font color=red>)</font color=red>
to add the closing parenthesis or you need to remove the open parenthesis:

=R7=MAX(\$R\$7:\$R\$36)

Steve

6. ## Re: Need Help Writing Formula (Excel 2000)

Yep, thanks. I will edit my reply.

7. ## Re: Need Help Writing Formula (Excel 2000)

Got it! Thanks for all the help!

Robert

8. ## Re: Need Help Writing Formula (Excel 2000)

Missing closing parenthesis added by HansV. Thanks to Steve (sdckapr) for pointing out the error.

If I understand your intent correctly (as Rudi points out, your (6) is ambiguous):

Select Q7:Q36 (with Q7 as active cell within the selection.
Select Format | Conditional Formatting...
Select Formula Is from the first dropdown list.
Enter the following formula in the box next to it.

<code>=(R7=MAX(\$R\$7:\$R\$36))</code>

Click Format...
Activate the Patterns tab.
Select the desired pattern.
Click OK to close the Format Cells dialog.
Click OK to close the Conditional Formatting dialog.

#### Posting Permissions

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