CODEWITHSUNDEEP
excelvba
JeffCh
JeffCh

Reputation: 95

Bold Top 50% of the summed values in Column E and their rows

After seeking help in stackoverflow, I came back with the skeleton of the vba codes (still not working though), however it is not generating the outcome that I desire. 

Sub Highlight_Top50()

    Dim CheckRange As range

    Set CheckRange = range("E2:E", Cells(Rows.Count, "E").End(xlUp)).Row

    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
    .TopBottom = xlTop10Top
    .Rank = 50
    .Percent = True
    End With

    With Selection.FormatConditions(1).Font.Bold = True

    End With

    Selection.FormatConditions(1).StopIfTrue = False

End Sub

Under Conditional Formatting, there's no such rule as "Highlight Top 50% of the summed value". The nearest Excel provides is "Format cells that rank in Top: x%".

Upvotes: 1

Views: 88

Answers (1)

PeterT
PeterT

Reputation: 8557

You're close. There are a couple of problems to fix.

The first is to ALWAYS make sure you're specifying a worksheet reference. Notice in the code below there is a '.' before ALL of the sheet references. This ensures you're referencing the sheet in the With clause.

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

Also, when you're counting the rows until the end of the data, the .Row was in the wrong place (outside of the )).

Next, you set up your CheckRange variable but you don't use it. You should avoid using Select. So just make the references for the FormatConditions to your CheckRange.

With CheckRange
    ...
End With

Here is the corrected method.

Sub Highlight_Top50()
    Dim CheckRange As Range
    With ActiveSheet
        Set CheckRange = .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
    End With

    With CheckRange
        .FormatConditions.AddTop10
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1)
            .TopBottom = xlTop10Top
            .Rank = 50
            .Percent = True
            .Font.Bold = True
            .StopIfTrue = False
        End With
    End With
End Sub

Upvotes: 1

Related Questions