Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: Rank Values By Bold Font

  1. #1
    Join Date
    28th April 2012

    Rank Values By Bold Font

    Hi all!

    In the below example i need to only rank the topline (bold records) and ignore all the other values from the value column when ranking. The excel rank formula ranks "office" as 3 because "detached" (which is a row to be ignored when ranking) has a higher value. It should however be ranked 2 as i have manually illustrated below...Note the bold rows are a total of the regular font rows directly below it...

    Any ideas on a formula that can help me rank only the values in bold while ignoring all other values from the column?


    Rank Description Value
    1 House 5,000,000
    Detached 3,000,000
    Semi 1,000,000
    Terrace 1,000,000
    2 Office 2,000,000
    1st Floor 1,000,000
    2nd Floor 1,000,000

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    19th July 2004
    Tokyo, Japan

    Re: Rank select cells only from a column

    UDF, if you like

    1) Hit Alt + F11 to open VBE
    2) go to [Insert] - [Module] then paste the code onto the right pane.
    3) hit Alt + F11 again to get back to excel

    Use like


    Function RankBold(myvalue, rng As Range, order) As Long 
        Dim x As Range, r As Range, n As Long 
        For Each r In rng.Columns(1).Cells 
            If r.Font.Bold Then 
                If x Is Nothing Then 
                    Set x = r(, 2) 
                    Set x = Union(x, r(, 2)) 
                End If 
            End If 
        RankBold = WorksheetFunction.Rank(myvalue, x, order) 
    End Function 

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Rows Font Style Bold
    By javed2011 in forum Excel General
    Replies: 5
    Last Post: March 19th, 2012, 03:15
  2. How to change the Font to Bold for specific criteria...
    By shalskedar in forum Excel General
    Replies: 2
    Last Post: July 29th, 2011, 09:47
  3. Bold font in visual basic editor
    By Darrenpowell in forum Excel General
    Replies: 3
    Last Post: March 30th, 2011, 04:57
  4. Printer Prints Non-Bold Font In Bold
    By gdrew in forum Excel General
    Replies: 3
    Last Post: May 6th, 2008, 10:58
  5. change font in bold for subtotals only in Pivottable
    By Paul20091968 in forum Excel General
    Replies: 6
    Last Post: July 3rd, 2006, 15:57


Posting Permissions

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