Highlight Values in sequentially descending order without using Sort.

ExcelAlumni

New Member
Joined
Jun 19, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Hoping someone can help. Looking for a conditional formatting solution for the following.
Have a long single column of data of differing numbers high & low and want to apply cell color format to highlight only the sequentially lower numbers in order down from the top to bottom.

So for e.g. A1 (1st number) is highlighted, (eg 238,654)
then second lowest down from that, let’s say it happens to be A3 (eg 237,234) (with A2 238655 ignored.)
then 3rd lowest etc…(eg 232,077) & any higher between 237,234 and 232,077 ignored.

In this manner the numbers get lower and lower towards zero with all subsequent higher numbers between each step getting ignored.
e.g. The highlighted numbers would start high and gradually get lower.
I don’t want to simply highlight the 10 lowest numbers in a column. I want to highlight numbers in the column in descending order with each new highlighted number always lower than the last.

- I do not want to use sort data options.

Alternatively an option to copy the numbers descending as described to an adjoining column; if so in same order and row position as original column.
I do not currently have the experience to be comfortable with VBA/Macros

Thanks in advance for any help or suggestions.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,707
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
At the risk of causing you discomfort, here's a simple macro that you can try if you care to. First, an example of its output for a short list of numbers from A2 to A30. If this is the result you want, the macro is below the example along with instructions for installing the macro.
Book2.xlsm
A
1Numbers
2273
3377
4938
5313
6564
7971
8753
9257
10227
11648
12863
13753
14406
15708
16757
17302
18893
19605
20974
21983
22108
23832
24896
25214
265
27640
28316
29463
30695
Sheet9

To install standard module code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.
VBA Code:
Sub HiliteValuesDescending()
Dim R As Range, V As Variant, i As Long, Mn As Variant, Rfill As Range
Set R = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
V = R.Value
Set Rfill = Range("A2")
Mn = Rfill.Value
For i = LBound(V, 1) + 1 To UBound(V, 1) - 1
    If V(i, 1) < Mn Then
        Mn = V(i, 1)
        Set Rfill = Union(Rfill, R(i))
    End If
Next i
If Not Rfill Is Nothing Then Rfill.Interior.Color = vbYellow
End Sub
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,686
Conditional Formatting can be used too. Borrowing JoeMo's list, and starting in row 1:

Book1
A
1273
2377
3938
4313
5564
6971
7753
8257
9227
10648
11863
12753
13406
14708
15757
16302
17893
18605
19974
20983
21108
22832
23896
24214
255
26640
27316
28463
29695
30
Sheet5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A180Expression=(A2<MIN(A$1:A1))*(A2<>"")textNO


Manually highlight A1. Then select A2:Awhatever, and enter the CF formula.
 

ExcelAlumni

New Member
Joined
Jun 19, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Thanks Greatly both for your superb suggestions & taking the time to investigate.
And yes with such detailed instructions I gained the confidence needed to brave attempting Macros - Thanks!
It worked - & also was apply to handle blanks in the column too.
I was able to adapt the macro to apply to other columns eg substituting all instances of 'A' with 'H' in the macro.

But one usage I neglected to mention was ideally to have the ability to refresh the rule further down the column.
So to reapply the rule (/macro/formula) of declining numbers at any chosen point further down the column.
So that the highlighted numbers previous highlighted above in the column are not changed but that from a new 'start' cell in the column to end of column the rule applies a new.
I tried experimenting but as macro newbie didn't know how to adjust macro so that achieve this degree of flexibility ie to become more relative & less absolute.
(Having to rewrite / insert new adjusted macro each time didn't seem practical.)

Eric W when I applied your formula it appeared to work but occasionally skipped 'next lowest' number decending number & highlighted instead a the very lowest number instead....
I believe this was something to do with 'Min' function & I believe the Formula for first occurrence instead needed to be inserted somewhere ($A$1:A1,A1)=1
but again being a novice my further experimentation proved unfruitful.

Any further tweaks would be immensely appreciated.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,707
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Thanks Greatly both for your superb suggestions & taking the time to investigate.
And yes with such detailed instructions I gained the confidence needed to brave attempting Macros - Thanks!
It worked - & also was apply to handle blanks in the column too.
I was able to adapt the macro to apply to other columns eg substituting all instances of 'A' with 'H' in the macro.

But one usage I neglected to mention was ideally to have the ability to refresh the rule further down the column.
So to reapply the rule (/macro/formula) of declining numbers at any chosen point further down the column.
So that the highlighted numbers previous highlighted above in the column are not changed but that from a new 'start' cell in the column to end of column the rule applies a new.

I tried experimenting but as macro newbie didn't know how to adjust macro so that achieve this degree of flexibility ie to become more relative & less absolute.
(Having to rewrite / insert new adjusted macro each time didn't seem practical.)
Any further tweaks would be immensely appreciated.
You are welcome - glad you gave macros a shot! With respect to the usage you neglected to mention, how is the cell that marks the start of a reapplied rule determined?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,686
Eric W when I applied your formula it appeared to work but occasionally skipped 'next lowest' number decending number & highlighted instead a the very lowest number instead....
I believe this was something to do with 'Min' function & I believe the Formula for first occurrence instead needed to be inserted somewhere ($A$1:A1,A1)=1
but again being a novice my further experimentation proved unfruitful.

Any further tweaks would be immensely appreciated.

Could you show an example of where it failed? And like JoeMo asked, how do you determine when to "restart" the rule?
 

ExcelAlumni

New Member
Joined
Jun 19, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Ok figured out how to use the XL2bb tool so here goes:
Here is eg of where conditional formatting failed for me. (But JoeMo's Macro did work)
Book3
A
1358
2348
3354
4355
5360
6361
7361
8361
9361
10356
11355
12358
13356
14356
15356
16360
17360
18361
19361
20322
21320
22320
23325
24330
25330
26329
27331
28333
29328
30329
31323
32324
33321
34318
35313
36327
37338
38335
39336
40352
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A41Expression=(A2<MIN(A$1:A1))*(A2<>"")textNO


In relation to "Restart rule".
Perhaps it's best to further explain fuller my application needs. Apologies both for not including this in first place & wasting your time. But I was trying to find the balance between figuring things out for myself & reaching out for help.

So I in actuality I have 2 Columns A & C showing results form 2 control groups.
Col A is where I am looking for the sequentially declining formula.
whilst Col C - I'm looking for occurrences of when there is an increase of 10 from last lowest result occurring in Col A

So overall this is what I want:

1) Instances of Col C being at least ‘10’ higher than Col A
In the above instance want Start Value in Col A to be highlighted via Cell Fill / Bold & corresponding Col C matching the 10 higher condition to highlighted too. So the match is easily identifiable.*

2) And I need the ‘reset’ conditions for the sequentially lower rule in COL A to be each time the above occurs.
i.e Once a difference of at least '10' occurs between COL A & COL C then the original declining rule resets from next New Cell on Col A.

The below is an eg of it in theory.
Note I have manually applied Green in COL A which represents the reset occurrences I am looking for
& COL C (bold) represent when COL C is at least 10 higher than lowest occurence in COL A.

Book6
AB
1358349
2348321
3354332
4355293
5360359
6361243
7361300
8350326
9361338
10356304
11355287
12358301
13346294
14356328
15337292
16360324
17225337
18361250
19341353
20322263
21320320
22320303
23325236
24330333
25330238
26329290
27331325
28333265
29328347
30329297
31323233
32324328
33321350
34318238
35313338
36327280
37338299
38335351
39336310
40352394
Sheet1


*Note : I also haven't figured out a formula conditional formatting rule to do the above yet either:
'Start Value in Col A to be highlighted via Cell Fill / Bold & corresponding Col C matching the 10 higher condition to highlighted too. So the match is easily identifiable.'
I know I need to apply =IF, =AND function so that in the instance that COL C is =<10 both the value in COL C & corresponding lowe Value in COL A that it corresponds to both highlight.
But don't have the expertise for it. My brain aches trying to work it out so any suggestions would be invaluable. And apologies again for not being clearer at start. Thanks again for your time & patience.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,686
The reason the CF didn't work for you is because you applied it to the range A1:A41, and I designed it to work on A2:A41. In order to get your new requirements to work, I had to add 2 helper columns. Consider:

Book2
ABCD
1358.334911
2348.3321 1
3354332 0
4355293 0
536035910
6361243 1
7361.3300 0
8350326 1
9361.1338 0
10355.8304 0
11355.004287 0
12358301 0
13346294 1
14356328 0
15337292 1
16360324 0
1722533711
18361.00125011
1934135311
20322.104263 1
21320.1320 1
22320.21303 0
23324.9236 0
24329.99833310
25330.201238 1
26329290 1
27331.015325 0
28333.005265 0
2932834711
30329297 1
31322.509233 1
32323.8328 0
33321.135011
34318.499238 1
35313.40133811
36327280 1
37338299 0
3833535110
39336.002310 1
4035239410
Sheet8
Cell Formulas
RangeFormula
C2:C40C2=IF(B2>MIN(INDEX(A:A,LOOKUP(2,1/C$1:C1,ROW(C$1:C1))):A2)+10,1,"")
D2:D40D2=(A2<>"")*(IF(C1=1,1,IF(A2<MIN(INDEX(A:A,LOOKUP(2,1/C$1:C1,ROW(C$1:C1))+1):A1),1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:AExpression=D1textNO


In C1 and D1, put the constant 1. Then put the C2 formula in and drag down the column. Put the D2 formula in and drag down the column. You can see that the C formula locates your "restart" points. But it varies slightly, check out row 18. Then using that column, the D formula decides which A cells need to be highlighted. Then the CF formula is very simple, it just points to the D column. You can/should select A1:A41 this time.

That's about the best I could do with CF. CF doesn't allow some features of formulas, which is why I couldn't insert the D formula directly into the CF rules table. If you don't want the C:D helper columns, perhaps JoeMo could update his macro. Good luck!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,707
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Here's a modified version of the code I posted earlier that is intended to address your broadened scope. Lightly tested on the data you show in post #7. My results tracks your expected results from post #7 except cell A21 is highlighted which I believe is correct and agrees with Eric W's CF solution.
VBA Code:
Sub HiliteValuesDescending2()
Dim Ra As Range, Va As Variant, Rb As Range, Vb As Variant, i As Long, Mn As Variant, Rfill As Range, Rbold As Range
Set Ra = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Set Rb = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
Va = Ra.Value: Vb = Rb.Value
Set Rfill = Range("A1")
Mn = Rfill.Value
If Vb(1, 1) >= Va(1, 1) + 10 Then
    Set Rbold = Range("B1")
    Set Rfill = Union(Rfill, Ra(2))
    Mn = Va(2, 1)
End If
For i = LBound(Va, 1) + 1 To UBound(Va, 1) - 1
    If Va(i, 1) < Mn Then
        Mn = Va(i, 1)
        Set Rfill = Union(Rfill, Ra(i))
    End If
    If Vb(i, 1) >= Mn + 10 Then
        If Rbold Is Nothing Then
            Set Rbold = Rb(i)
        Else
            Set Rbold = Union(Rbold, Rb(i))
        End If
        Set Rfill = Union(Rfill, Ra(i + 1))
        Mn = Va(i + 1, 1)
    End If
Next i
If Va(UBound(Va, 1), 1) < Mn Then
    Set Rfill = Union(Rfill, Ra(UBound(Va, 1)))
    Mn = Va(UBound(Va, 1), 1)
End If
If Vb(UBound(Va, 1), 1) >= Mn + 10 Then Rb(UBound(Va, 1)).Font.Bold = True
Application.ScreenUpdating = False
If Not Rfill Is Nothing Then Rfill.Interior.Color = vbYellow
If Not Rbold Is Nothing Then Rbold.Font.Bold = True
Application.ScreenUpdating = True
End Sub
Book2.xlsm
AB
1358349
2348321
3354332
4355293
5360359
6361243
7361300
8350326
9361338
10356304
11355287
12358301
13346294
14356328
15337292
16360324
17225337
18361250
19341353
20322263
21320320
22320303
23325236
24330333
25330238
26329290
27331325
28333265
29328347
30329297
31323233
32324328
33321350
34318238
35313338
36327280
37338299
38335351
39336310
40352394
Sheet9
 

ExcelAlumni

New Member
Joined
Jun 19, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Thanks immensely both for your generosity over this. It has been a huge help.
Eric W, I experimented with the conditional formatting method on an extended range & it seemed to be about 90-95% effective.
As Well as row 18 see Row 46, 68 & 73 in below. (Unless of course I've neglected something which is highly likely).
Cond Format TEST 2.xlsm
ABCD
1358.334911
2346321 1
3354332 0
4355293 0
5360350 0
6361243 0
7361.3300 0
8350326 0
9361.1338 0
10355.8304 0
11355.004287 0
12358301 0
13346294 0
14356328 0
15337292 1
16360324 0
1722533711
18361.00125011
19341347 1
20322.104263 1
21320.1320 1
22320.21303 0
23324.9236 0
24329.99833310
25330.201238 1
26329290 1
27331.015325 0
28333.005265 0
2932834711
30329297 1
31322.509233 1
32323.8328 0
33321.135011
34318.499238 1
35313.40133811
36327280 1
37338299 0
3833535110
39336.002310 1
4035239410
4137238611
4235737511
43330307 1
4430333111
4528043211
4629729811
4727833311
4829335311
4929534011
5026432711
5131831911
5230436011
5338342011
5426733711
5527637611
5626041111
5734233711
5828334011
5934643011
60373352 1
6125834511
6237730911
63381368 1
64385308 0
6535940311
6629939711
6724737111
6836336311
6927336811
7037630011
7131842011
7230234811
7336232211
74356330 1
7529639211
7638435711
7732939911
7830432311
7925541011
8029542711
8132434711
8230338011
8335836411
84350346 1
85376304 0
8624631511
8724537411
8836935511
8932841111
9029737111
9125735411
9232339711
9330839311
9435232411
9525439811
9634435111
9738539711
9832736911
9926636711
10033741611
10136836711
10225237311
10335839011
10429536711
10535132311
106361304 1
10726540111
10837342911
10926041511
11035735011
111313315 1
11240141510
11329140011
11428641411
11539935611
11639941411
117367323 1
11832139911
11928540411
12035038211
12133541011
12228042211
12341636111
12430032611
12530638111
12633733111
12729632911
12831737511
12927431911
13031337911
13137339111
132409332 1
133329311 1
13428629811
13539134111
13631138911
137345305 1
13831036011
13929734711
14028133411
14131441111
14230735411
14328433211
14430030911
14535638611
14633638111
14741240111
14833939311
14934941811
15029130211
15131841211
15232541211
15331332911
15436433511
15531233811
15629633511
15727836211
15834136311
15928634411
16026230111
16129242311
16235136011
163386319 1
164408332 0
16527432511
16628540211
16734439911
16827438711
16929332611
17027338111
17135530911
17235241411
173365318 1
17438740710
175351352 1
17627535411
17731738411
17826842011
17937934911
18032235411
18132941911
182378311 1
183375337 1
18430436911
18536332211
186408311 1
18730335611
18832038811
18933134311
19040836611
19126732311
19230338111
19337939311
19440343111
195406353 1
19627835811
19737841611
19829734211
19934337211
20027143011
20133539211
20226332411
20339729911
20433835711
20528141211
20637033011
207344350 1
20827033111
20928036911
21028733011
21132539011
212387329 1
21328134111
21430435411
21526737411
21638334811
21738042011
218373349 1
219362321 1
22030337511
22138142311
222339339 1
22339636810
22427339511
22537534111
22634442211
22726441511
22838339511
22931341211
230403322 1
231407318 0
23232638811
23329630811
23440433411
235317298 1
23633542410
23732438011
23834837211
23935642011
240398334 1
24131739711
24239038811
243334332 1
24428039211
24532835811
24626338311
24737542511
248363301 1
24932442611
250372325 1
25137040311
25229638011
25336631211
254391299 1
255371350 1
25637938010
257358354 1
Sheet1
Cell Formulas
RangeFormula
C2:C257C2=IF(B2>MIN(INDEX(A:A,LOOKUP(2,1/C$1:C1,ROW(C$1:C1))):A2)+10,1,"")
D2:D257D2=(A2<>"")*(IF(C1=1,1,IF(A2<MIN(INDEX(A:A,LOOKUP(2,1/C$1:C1,ROW(C$1:C1))+1):A1),1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B257Expression=$C2=1textNO
A:AExpression=D1textNO


JoeMo, thanks the updated VBA worked fantastically for my needs.
I tried adapting it in the instance I was looking for an 80 value difference between my Control 1 & 2 (Column A & Column B) and managed to achieve this:
Module Test &80.xlsm
AB
13120531435
23116031382
33121131300
43121131311
53110031301
63109531225
73110530373
83219431310
93125031310
103120031310
113120031365
123122131322
133126431365
1431381
153125031181
163121530324
173113531303
183115331250
193120530287
203124631318
213124631320
223120031286
233121030337
2431460
2531491
2631465
2731458
2831433
2931472
3031695
3131699
3231734
3331646
3431673
3531715
3631665
3731666
3831645
3931656
4031715
4131664
4231627
4331585
4431548
4531500
4631493
4731560
4831564
4931588
5031655
5131620
5231575
5331581
5431600
5531595
5631595
5731582
583130030549
593530031510
603130031422
613138931476
6231515
6331594
6431598
6531669
6631650
6731670
683142531662
693142531561
703141631558
Sheet1


As an adaption. I also tried to highlight different parameters eg where COL B was 2.5% more than COL A but didn’t know how to adapt the VBA macro to express a percentage increase as opposed to a value increase. Any tips would be appreciated. Also how can I adapt the macro to different columns ie not just COL A & B?

Only leaves me to again thank you both once again for your rapid response & application to my problem as a newcomer you have made me feel very welcome on the forum.
 

Forum statistics

Threads
1,148,342
Messages
5,746,192
Members
423,997
Latest member
eakenila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top