Count a range of labels, split in half and highlight the top half

Smitty · Feb 26th 2015

I have an odd'ish request. I will receive Excel files that will contain anywhere from 10-40 labels (there is data, but for this, it's immaterial). I need to count the number of labels, determine the top and bottom range, and shade the top (at a glance, they user can see who is in the top half of that particular list). The split is not based on anything data-related (which I've fiddled with conditional formatting based on data, so I get that), just simply "who is in the top half" of that specific list. The extra odd thing is I need if it's an uneven split, to remove the extra row from the top half and leave it in the unhighlighted bottom half.

Any help would be much appreciated, because I don't even know how to go about doing this since it's not data related. Thanks!!

ashu1990 · Feb 26th 2015

Re: (newbie) Count a range of labels, split in half and highlight the top half

attach a workbook with the input data and the out put data as required..

Smitty · Feb 26th 2015

Re: (newbie) Count a range of labels, split in half and highlight the top half

Here is an illustration of what I have to give back... forum.ozgrid.com/index.php?attachment/64186/

40 rows, top 20 highlighted. The next one could be 35 rows with the top 17 highlighted. Make sense?

ashu1990 · Feb 26th 2015

Re: Count a range of labels, split in half and highlight the top half

here you go

Code

Sub test()
Dim cnt As Double




cnt = WorksheetFunction.RoundDown(WorksheetFunction.CountA(Range("A:A")) / 2, 0)


Range("A:C").Interior.Color = xlNone


With Range("A1:C" & cnt).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
End With




End Sub

Display More

Smitty · Feb 27th 2015

Re: Count a range of labels, split in half and highlight the top half

Thank you very much! I have to admit I don't know exactly how this helps though. I currently have all my conditional formatting applied to the cells, so when the programmer funnels the data in, it automatically adjusts to highlight/bold/whatever I need in that instance, and I don't have to do anything after receiving it. (I've applied it to the template itself before the data arrives) Is there anyway to do that directly in Excel like that? Or could this work like that and I just don't know it?

Thanks!

Count a range of labels, split in half and highlight the top half

Participate now!

Share