+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting Question

  1. #1
    Registered User
    Join Date
    01-31-2007
    Posts
    3

    Conditional Formatting Question

    I have a range A1:A20. Each cell in this range contains a number between 1 and 100. I'd like to be able to format this range so that the top 3 or largest 3 numbers appear in red. Is there a way to do this? Thanks!

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    I'm not sure how to do this with 1 condition, but here is how to do it with 3 (Formula Is):

    =A1=LARGE($A$1:$A$20,1)

    =A1=LARGE($A$1:$A$20,2)

    =A1=LARGE($A$1:$A$20,3)

  3. #3
    Registered User
    Join Date
    01-31-2007
    Posts
    3
    Tried those conditions but that just turned the number in A1 red.

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try

    =RANK(A1,$A$1:$A$100)<=3

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Select the column first before applying the CF

    VBA Noob

  6. #6
    Registered User
    Join Date
    01-31-2007
    Posts
    3
    That still only formats the 1st cell in the range. The format that I'd like to apply is to change the color of the the 3 largest numbers in the range A1:A20 to red. Thanks

  7. #7
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Select cell A1. Then hold down SHIFT and CTRL and hit the down arrow. This will highlight A1:A20, with A1 as the active cell. Now go to conditional formatting and use VBA Noob's formula above.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1