# Thread: Conditional Formatting: Top 4 as a Percent (Excel

1. ## Conditional Formatting: Top 4 as a Percent (Excel

I looked at the information in <post#=682277>post 682277</post#> and it's close... but not quite what I was looking for. I confess to knowing very little about VBA, so let me pose a question here and if there's a reference on the boards, feel free to point me to it. I have a feeling that what I'm trying to accomplish will require a macro.

I have a spreadsheet with several (5-6) individual sheets. These are used to track inventory by make and model across different car dealerships. For the market being reviewed, I would like to highlight the top four as a percent of dealer inventory, which are in vertical columns. Additionally, horizontally across the sheet, the dealer with the largest number of any given model would be highlighted a different color.

I've attached a sample worksheet that shows the concept. Conditional formatting is too limited to accomplish this task, it would seem.

2. ## Re: Conditional Formatting: Top 4 as a Percent (Excel

You can use conditional formatting for this - see the second sheet in the attached workbook (I left the first for comparison). I set the conditional formatting formula for B6:20 to

=AND(B6>0,B6=MAX(\$B6,\$D6,\$F6,\$H6,\$J6,\$K6,\$L6,\$N6,\$ P6,\$R6))

and that for C6:C20 to

=C6>=LARGE(C\$6:C\$20,4)

then used Copy and Paste Special with the Formatting option to copy the formatting to the rest of the table.

3. ## Re: Conditional Formatting: Top 4 as a Percent (Ex

Thanks Hans, worked a charm. I didn't realize I could use complex formulas in conditional formatting, but then it wouldn't have mattered either, since I would still have had to ask how to identify the information the way I wanted! Time to start learning some advanced Excel features.

#### Posting Permissions

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