You are here:

# Excel/Conditional format formula

Question
Hi Tom

I have a range of cells: C21:K21.  I wish to conditionally format these cells so that the cell that contains the highest number triggers a format.

So, if the number in cell C21 is greater than any number in cells D21:K21, then (condition).

Or, if the number in cell D21 is greater than any number in cells C21 or E21:K21, then (condition).

Could you show me how this formula should be written?

Chris

Christopher Mitchell

for the condition for cell C21

=max(\$C\$21:\$K\$21)=C\$21

If you select C21:K21 with C21 as the active cell and set up your conditional formatting, then then the highest value will be highlighted (if that is the formatting you choose).

Note the  C\$21 does not have the \$ sign so it will adjust to reflect the cell which holds the conditional formatting.  Again, you would change the C\$21 to refer to the activecell in the selection when you apply the conditional formatting.

Now this will highlight any cell in that range that holds the highest value.  So if two cells held the same highest value, then it would highlight both.

--
Regards,
Tom Ogilvy
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Excellent. Thank you

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.