Results 1 to 3 of 3
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    Cincinnati, Ohio, USA
    Posts
    7,089
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files
    -Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.
    Attached Files Attached Files

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    Cincinnati, Ohio, USA
    Posts
    7,089
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    -Mark

Posting Permissions

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