CODEWITHSUNDEEP
excelvba
JeffCh
JeffCh

Reputation: 95

Bold the top 50% of the summed values and their rows

I am still new to vba macro and I have a set of data in number that I want to do the following with Vba:

  1. Sort them from largest to smallest (Done)

  2.  

    • Bold the top 50% of the summed values (Bold the whole row). AND
    • If any value ≥ x, bold it too.

Example: Column A has a sum of values = 16 millions, I only want to bold the row(s) wherein the cells in Column A contain values summing up to 8 millions. AND If there are any cells in Column A contain value ≥ 1 Million, bold them as well.

The starting formula I can think of for (2) is: 

= LARGE(title, {1;2;3...})

But knowing that this formula is only for retrieving nth largest value, it does not help.

Upvotes: 0

Views: 55

Answers (2)

JeffCh
JeffCh

Reputation: 95

Sub Highlight_Top50_AND_500K()

    Dim CheckRange As Range
    Dim x As Range
    Dim formula1 As Long

    With ActiveSheet
        Set CheckRange = .Range("M2:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)

    End With

    On Error Resume Next
    Range("M1").Sort Key1:=Range("M2"), _
      Order1:=xlDescending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom

With CheckRange
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, formula1:="1000000"
    With .FormatConditions(1)
        .Font.Bold = True
        .StopIfTrue = False
    End With
    .FormatConditions.Add Type:=xlExpression, formula1:="=(SUM($M$2:M2)-0.99*M2)<=0.5*SUM(M:M)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1)
         .Font.Bold = True
         .StopIfTrue = False
    End With

End With

End Sub

Upvotes: 0

3xGuy
3xGuy

Reputation: 2559

You can do this with conditional formatting.

  1. Open Excel
  2. Select The Range that you wish to format
  3. press Alt+O+D
  4. Select "Format Only Top or Bottom Ranged Values"
  5. change 10 to 50 then check the % of the selected range.
  6. Click the format button
  7. Select Font
  8. Select Bold

if you're wanting to use a Macro because of it being dynamic, you can use a table and just select the entire column of the table.

Upvotes: 1

Related Questions