Rank Values By Bold Font

[ExBeginner]

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?

Example:

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

[jindon]

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

=IFERROR(RankBold(C2,$B$2:$C$8,0),"")

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

RankBold.xlsm