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.
 
Thanks immensely both for your generosity over this. It has been a huge help.
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)

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.
You are welcome. Here's a modification that will allow you to choose whether you want to use a criterion of col B >= min + Delta to bolden col B
where delta is an adder like 10 or 80 ....., or a multiplier like
col B >= min*Percent to bolden col B
where Percent is like 1.025 ... so that col B must be at least 2.5% more than the min.
Note that the constants Delta and Percent are defined just below the Sub line so that you can adjust them to whatever you like w/o having to make multiple adjustments in the body of the code when you wish to change them.

You will also get a pop-up input box when you run the code in which you must specify which of the two constants you want to employ. You must respond with one of the two and the response is case sensitive. You can also click cancel to exit the sub.

To change the columns simply replace references in the body of the code to either with the column letter(s) of your choice. If you expect to do this frequently, let me know and I can provide some additional modifications to allow you to input which two columns you want to use.
VBA Code:
Sub HiliteValuesDescending3()
Const Delta As Double = 10  'the amount by which col B must exceed col A to bolden col B entry
Const Percent As Double = 1.025 ' Col B must exceed col A by (Percent x col A) to bolden col B entry
Dim Ra As Range, Va As Variant, Rb As Range, Vb As Variant, i As Long, Mn As Variant
Dim Rfill As Range, Rbold As Range, Ans As String
Ans = InputBox("Which constant do you want to use: Delta or Percent?")
If Ans = "" Then Exit Sub
If Ans <> "Delta" And Ans <> "Percent" Then
    MsgBox "You must enter either Delta or Percent - try again."
    Exit Sub
End If
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) >= IIf(Ans = "Delta", Va(1, 1) + Delta, Percent * Va(1, 1)) 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) >= IIf(Ans = "Delta", Mn + Delta, Percent * Mn) 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) >= IIf(Ans = "Delta", Mn + Delta, Percent * Mn) 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
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
For the CF, try this variation on the C2 formula:

=IF(B2>MIN(INDEX(A:A,LOOKUP(2,1/C$1:C1,ROW(C$1:C1)+1)):A2)+10,1,"")

Put in C2 and drag down. As far as I can tell, it corrects the rows you mentioned.
 
Upvote 0
Great Thanks Eric W - Yes that corrected the issue, worked like a charm.

Again thanks both for your continued help on this & being such an asset to the forum and larger community.

JoeMo, It feels good to be dipping my toe in the world of VBA for the first time, feels like the start of an ongoing adventure (lol).

I input the code correctly and entered case sensitive answer. Worked fine for the changing Delta as double Value but as far as I can see the results don’t tally for the percentage increase. (Or more than likely the explanation of my needs doesn’t tally with your code!).
Multiplier 2.5.xlsm
AB
13120531434.85
23116031382
331210.631300
431210.631310.6
531099.6531301.2
63109531225
73110530373.45
832193.5531309.95
93125031310
103120031310
113120031365
1231220.5531321.7
1331263.6531365
1431380.5
153125031180.55
163121530324.2
1731134.531302.9
1831152.6531249.9
193020530287.05
2031245.831318.45
2131245.831320
2231200.1531285.55
233121030336.7
2431460
2531891.25
2631465
2731458.15
2831432.55
2931471.9
3031695
3131699.4
3231734.4
3331645.75
3431672.8
3531715
3631665.4
3731665.8
3831645
3931656.45
4031715.35
4131664.35
4231626.6
4331584.85
4431548.3
4531500
4631492.9
4731559.95
4831564.35
4931587.55
5031655
5131619.55
5231574.85
5331581.2
5431600
5531594.6
5631594.5
5731581.55
583130030549.2
592930031510.3
603130031422.3
6131389.3531476.35
6231515.05
6331593.9
6431597.6
6531669.45
6631649.75
6731670
683142531662.3
693142531560.8
7031415.6531557.85
Sheet1

So for the ‘Percent as double’ value' I’m looking for the Col B results to be for e.g. 2.25% higher than the current lowest declining value on Col A.
So for e.g. Cell A6 31095 +2.25% = 31794.64 (or 31095 x 1.0225=31794.64) would expect first highlighted cell to be 31794 but Cell B14 is highlighted.

I tried with a lower Value in code eg. Looking for Col B results returning when they are 1.5% higher than the last lowest value in Col A by substituting the following in the code : Const Percent As Double = 1.015 ' Col B must exceed col A by (Percent x col A) to bolden col B entry.

But again received following results, which don’t seem to tally.
Multiplier 2.5.xlsm
DE
13120531434.85
23116031382
331210.631300
431210.631310.6
531099.6531301.2
63109531225
73110530373.45
832193.5531309.95
93125031310
103120031310
113120031365
1231220.5531321.7
1331263.6531365
1431380.5
153125031180.55
163121530324.2
1731134.531302.9
1831152.6531249.9
193020530287.05
2031245.831318.45
2131245.831320
2231200.1531285.55
233121030336.7
2431460
2531891.25
2631465
2731458.15
2831432.55
2931471.9
3031695
3131699.4
3231734.4
3331645.75
3431672.8
3531715
3631665.4
3731665.8
3831645
3931656.45
4031715.35
4131664.35
4231626.6
4331584.85
4431548.3
4531500
4631492.9
4731559.95
4831564.35
4931587.55
5031655
5131619.55
5231574.85
5331581.2
5431600
5531594.6
5631594.5
5731581.55
583130030549.2
592930031510.3
603130031422.3
6131389.3531476.35
6231515.05
6331593.9
6431597.6
6531669.45
6631649.75
6731670
683142531662.3
693142531560.8
7031415.6531557.85
Sheet1

In fact only difference was absence of highlighting of Row 20 : 31318.45 & Row 59.
As I say most likely down to my poor coms or inputs. If you haven't run out of patience with me yet, please let me know if there is something obvious I am doing wrong. Also thanks, substituting rows in code was absolutely fine for my needs.
 
Upvote 0
I think the problem is my not understanding how you want to handle the blank (empty) cells in col A. Right now they are, in effect, seen as having the value 0 which the code interprets as being a new minimum. Clearly that's not what you want. To modify the code I need you to answer the following questions.

1. Do you want to ignore the blank cells in Col A so that they are not treated as having a lower value than the existing minimum value from the col A cells above them?
2. Will there ever be a data set where the first col A cell is empty, i.e. there's a value in col B first cell, but the companion A cell is blank?
 
Upvote 0
Hey JoeMo - thanks for quick response. Sorry for lack of clarity.
It's because I don't have a coders brain & tbh I've been overwhelmed by your conscientious & in depth responses (just wasn't expecting it).

So...
1) Yes blank Cells to be always ignored.
2) There will never be a data set where Col A first cell is empty (whilst B has a value) ie A1 will always have a value.
3) Thanks again!
 
Upvote 0
Hey JoeMo - thanks for quick response. Sorry for lack of clarity.
It's because I don't have a coders brain & tbh I've been overwhelmed by your conscientious & in depth responses (just wasn't expecting it).

So...
1) Yes blank Cells to be always ignored.
2) There will never be a data set where Col A first cell is empty (whilst B has a value) ie A1 will always have a value.
3) Thanks again!
Here's a revision that's intended to ignore blank cells in Col A. Very lightly tested so I'll leave it to you to see if it's doing what you want. If not, maybe you can provide a short data set that includes things that make it stumble and then in say cols D&E show the results you want using manual color fill and boldening.
VBA Code:
Sub HiliteValuesDescending4()
Const Delta As Double = 10  'the amount by which col B must exceed col A to bolden col B entry
Const Percent As Double = 1.0225 ' Col B must exceed col A by (Percent x col A) to bolden col B entry
Dim Ra As Range, Va As Variant, Rb As Range, Vb As Variant, i As Long, Mn As Variant
Dim Rfill As Range, Rbold As Range, Ans As String
Ans = InputBox("Which constant do you want to use: Delta or Percent?")
If Ans = "" Then Exit Sub
If Ans <> "Delta" And Ans <> "Percent" Then
    MsgBox "You must enter either Delta or Percent - try again."
    Exit Sub
End If
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) >= IIf(Ans = "Delta", Mn + Delta, Percent * Mn) Then
    Set Rbold = Range("B1")
    If Not IsEmpty(Va(2, 1)) Then
        Set Rfill = Union(Rfill, Ra(2))
        Mn = Va(2, 1)
    End If
End If
For i = LBound(Va, 1) + 1 To UBound(Va, 1) - 1
    If Not IsEmpty(Va(i, 1)) Then
        If Va(i, 1) < Mn Then
            Mn = Va(i, 1)
            Set Rfill = Union(Rfill, Ra(i))
        End If
        If Vb(i, 1) >= IIf(Ans = "Delta", Mn + Delta, Percent * Mn) Then
            If Rbold Is Nothing Then
                Set Rbold = Rb(i)
            Else
                Set Rbold = Union(Rbold, Rb(i))
            End If
            If Not IsEmpty(Va(i + 1, 1)) Then
                Set Rfill = Union(Rfill, Ra(i + 1))
                Mn = Va(i + 1, 1)
            End If
        End If
    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) >= IIf(Ans = "Delta", Mn + Delta, Percent * Mn) 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
 
Upvote 0
Hi JoeMo,
So yes ran a few more test & ran into 3 (probably related issues with data sets).

EG 1 – Running the VBA code to highlight any second Column Value that is 2.25% higher than last lowest value in 1st Column.
Here COLS F & G show I have manually highlighted egs of where I believe code should have highlighted:

Bold G143 (representing D143) But then C426 is highlighted yellow so it’s as if it recognizes part of the rule.
Also both Bold G490/5 are 2.25% more than the last value C482 which in turn would have meant C515 should have then been highlighted yellow.
490 test(C).xlsm
CDEFG
7638443849.1438443849.14
773848.2553858.1253848.2553858.125
7838503863.538503863.5
793850.0053858.2753850.0053858.275
803846.5853855.293846.5853855.29
813837.4853853.4953837.4853853.495
823840.0938493840.093849
833840.413848.253840.413848.25
843847.5653850.73847.5653850.7
853848.2153852.5853848.2153852.585
863842.83851.743842.83851.74
873840.53849.9853840.53849.985
883844.493856.633844.493856.63
893852.243857.443852.243857.44
903853.2153863.013853.2153863.01
913856.553865.6153856.553865.615
923852.53868.693852.53868.69
9338503864.2738503864.27
943839.1953856.923839.1953856.92
953846.6653856.153846.6653856.15
963853.3538603853.353860
973850386038503860
983847.5153859.9953847.5153859.995
993851.483859.43851.483859.4
1003854.433862.53854.433862.5
1013858.6653860.8453858.6653860.845
1023856.343866.5153856.343866.515
1033856.6853863.1453856.6853863.145
1043856.573863.483856.573863.48
1053849.943864.9353849.943864.935
10638443857.12538443857.125
1073848.713852.123848.713852.12
1083844.00538553844.0053855
1093839.1853854.9953839.1853854.995
1103841.43851.8953841.43851.895
11138413847.51538413847.515
1123846.3053851.23846.3053851.2
1133847.5053856.423847.5053856.42
1143848.5253856.423848.5253856.42
1153843.53856.9053843.53856.905
1163841.53851.8453841.53851.845
1173835.013849.093835.013849.09
1183834.9853845.7053834.9853845.705
1193837.8053844.9953837.8053844.995
1203840.85538453840.8553845
1213823.4753848.493823.4753848.49
1223820.2653842.6753820.2653842.675
1233810.823830.363810.823830.36
1243816.413824.53816.413824.5
1253817.933827.5253817.933827.525
1263819.7453827.63819.7453827.6
1273820.53829.473820.53829.47
1283816.2353829.733816.2353829.73
1293815.0053828.753815.0053828.75
1303813.0453822.2853813.0453822.285
13137983821.11537983821.115
1323805.53818.9353805.53818.935
1333808.8253823.363808.8253823.36
1343822.5153831.033822.5153831.03
1353827.7053848.0153827.7053848.015
1363843.2253894.4553843.2253894.455
1373893.5053957.913893.5053957.91
13839183972.239183972.2
1393957.9053957.905
1403966.463966.46
1413973.493973.49
1423974.53974.5
1434008.1854008.185
14440104010
1454009.8254009.825
1463994.23994.2
1473988.7553988.755
1483976.8253976.825
1493983.3053983.305
1503985.6853985.685
1513985.9653985.965
1523969.753969.75
1533972.0353972.035
1543975.253975.25
15539773977
1563978.5853978.585
1573978.993978.99
1583980.873980.87
1593978.53978.5
1603962.323962.32
1613962.063962.06
1623963.2353963.235
1633959.6353959.635
1643954.8653954.865
1653950.863950.86
1663951.053951.05
1673955.013955.01
1683962.5253962.525
1693966.6653966.665
1703962.9553962.955
1713956.133956.13
1723965.443965.44
1733968.073968.07
1743967.3653967.365
1753964.0153964.015
1763960.5653960.565
1773964.213964.21
17839683968
1793965.913965.91
1803962.063962.06
1813961.453961.45
1823959.5453959.545
1833957.023957.02
1843947.3553947.355
1853945.5753945.575
1863948.8853948.885
1873949.9653949.965
1883951.823951.82
1893952.8953952.895
1903957.2253957.225
1913958.4953958.495
1923959.953959.95
1933972.963972.96
19439753975
1953980.8653980.865
1963966.673966.67
1973967.53967.5
1983965.7153965.715
1993958.9553958.955
2003961.2753961.275
2013955.1653955.165
2023956.5953956.595
2033961.293961.29
2043962.643962.64
2053963.5553963.555
20639603960
2073957.53957.5
2083958.5253958.525
2093951.2553951.255
2103952.33952.3
2113953.563953.56
2123956.483956.48
2133959.6753959.675
2143956.8553956.855
2153957.6653957.665
2163955.4853955.485
2173952.753952.75
2183955.9153955.915
2193959.9953959.995
2203963.383963.38
2213966.6153966.615
2223969.283969.28
2233968.773968.77
22439653965
2253961.863961.86
2263961.993961.99
2273962.243962.24
2283963.133963.13
2293966.983966.98
2303966.983966.98
23139673967
2323966.53966.5
2333965.6253965.625
2343974.9353974.935
2353981.653981.65
2363986.583986.58
2373991.4253991.425
2383990.083990.08
2393984.9553984.955
2403986.253986.25
2413989.7353989.735
24239873987
2433984.2753984.275
2443980.943980.94
2453972.53972.5
24639643964
2473956.563956.56
2483951.5953951.595
2493948.7953948.795
2503956.1853956.185
2513962.413962.41
2523957.0453957.045
2533954.4253954.425
2543952.423952.42
2553952.4353952.435
2563957.13957.1
2573950.463950.46
2583954.0253954.025
2593960.6253960.625
2603960.6253960.625
2613960.9753960.975
2623960.8853960.885
2633964.53964.5
2643966.4053966.405
2653972.53972.5
2663972.743972.74
26739753975
2683972.2253972.225
2693970.893970.89
2703965.983965.98
2713965.153965.15
2723965.7353965.735
2733971.6353971.635
2743971.6353971.635
2753976.463976.46
2763967.4353967.435
2773968.593968.59
2783969.4353969.435
2793974.53974.5
2803978.363978.36
2813987.673987.67
2823987.53987.5
28339883988
2843988.4953988.495
2853984.443984.44
2863991.3753991.375
2873996.33996.3
28840004000
28940054005
2903991.9253991.925
2913978.8253978.825
2923979.8253979.825
2933978.9853978.985
2943978.53978.5
2953978.5453978.545
2963978.53978.5
2973974.663974.66
2983980.8253980.825
2993980.8253980.825
3003983.633983.63
3013982.433982.43
3023976.673976.67
3033974.153974.15
3043974.4953974.495
3053974.53974.5
30639703970
3073969.9953969.995
3083969.9953969.995
30939673967
31039683968
3113971.4553971.455
3123973.1453973.145
3133971.973971.97
31439723972
3153971.393971.39
3163967.193967.19
3173967.0653967.065
3183967.863967.86
3193968.333968.33
3203965.173965.17
3213958.4253958.425
3223960.673960.67
32339633963
3243964.1253964.125
3253966.53966.5
3263964.6153964.615
3273960.9453960.945
3283955.473955.47
3293961.173961.17
3303962.3453962.345
3313963.243963.24
3323966.323966.32
3333967.423967.42
3343967.5553967.555
3353967.043967.04
3363967.4953967.495
3373968.253968.25
3383967.393967.39
3393970.4853970.485
3403970.083970.08
3413969.8853969.885
34239693969
3433967.9353967.935
3443963.1853963.185
3453964.283964.28
3463964.0953964.095
3473962.413962.41
3483958.7753958.775
3493961.3753961.375
3503959.373959.37
3513959.753959.75
3523954.8653954.865
3533956.1053956.105
3543960.453960.45
3553966.283966.28
3563966.5253966.525
3573966.023966.02
3583965.533965.53
3593963.6053963.605
3603960.9153960.915
3613956.223956.22
3623957.2553957.255
3633956.6653956.665
3643955.083955.08
36539503950
3663949.833949.83
3673953.253953.25
36839503950
3693949.4153949.415
3703952.0453952.045
3713948.4653948.465
37239453945
3733944.3653944.365
3743937.8853937.885
37539403940
3763947.5153947.515
37739503950
3783950.2553950.255
3793950.3053950.305
3803954.8453954.845
3813954.8453954.845
3823958.353958.35
3833954.53954.5
3843950.043950.04
3853947.53947.5
3863948.6153948.615
3873949.963949.96
3883955.773955.77
38939563956
3903957.7053957.705
3913959.0253959.025
3923959.2753959.275
3933961.773961.77
3943961.983961.98
3953961.6653961.665
3963965.3653965.365
3973968.043968.04
3983965.943965.94
3993957.283957.28
4003956.9153956.915
4013954.9753954.975
4023957.533957.53
4033958.4853958.485
40439603960
4053956.43956.4
4063953.583953.58
4073951.53951.5
4083949.5553949.555
4093952.5053952.505
4103943.7653943.765
4113894.8053894.805
4123900.9953900.995
4133902.113902.11
41438963896
4153896.583896.58
4163899.2153899.215
41738973897
4183899.53899.5
41938993899
4203902.53902.5
4213893.53893.5
4223896.543896.54
4233899.0753899.075
4243897.993897.99
4253896.943896.94
4263861.53894.3453861.53894.345
4273880.9553880.955
4283860.538803860.53880
4293853.4153875.0853853.4153875.085
4303884.6353884.635
4313884.663884.66
4323865.1338853865.133885
4333871.643885.013871.643885.01
4343875.133881.6553875.133881.655
4353871.0853882.3853871.0853882.385
4363865.693877.463865.693877.46
4373868.253874.9753868.253874.975
4383871.3253882.9353871.3253882.935
43938803883.21538803883.215
4403883.0753891.0253883.0753891.025
4413892.973892.97
4423881.063892.4953881.063892.495
4433878.5653890.8453878.5653890.845
4443884.6253889.133884.6253889.13
4453885.233894.4953885.233894.495
4463894.4953894.495
4473882.52538953882.5253895
4483881.693892.7253881.693892.725
4493883.8353888.183883.8353888.18
4503892.523892.52
4513896.253896.25
4523884.2353896.3453884.2353896.345
4533885.023892.8753885.023892.875
4543892.53892.5
4553892.53892.5
4563875.7253900.353875.7253900.35
4573865.53893.473865.53893.47
4583869.4953882.143869.4953882.14
4593875.6153881.4953875.6153881.495
4603875.9153884.683875.9153884.68
4613879.173884.683879.173884.68
4623879.8353885.1753879.8353885.175
4633879.4253886.173879.4253886.17
4643885.393888.0753885.393888.075
46538853892.4238853892.42
4663880.5453890.3853880.5453890.385
4673882.473887.373882.473887.37
4683885.6553888.8853885.6553888.885
46938853890.61538853890.615
4703878.1453892.453878.1453892.45
4713872.53890.953872.53890.95
4723868.933882.4853868.933882.485
4733872.53880.2253872.53880.225
47438653879.8238653879.82
4753865.5153874.823865.5153874.82
4763868.33878.443868.33878.44
4773877.453882.1653877.453882.165
4783879.673883.3253879.673883.325
4793880.253887.883880.253887.88
4803880.183888.4353880.183888.435
4813867.723888.3653867.723888.365
4823846.1153883.1153846.1153883.115
4833860.033869.1553860.033869.155
48438603872.8938603872.89
4853863.293869.9953863.293869.995
48638653884.7838653884.78
4873880.3053888.3353880.3053888.335
4883883.563892.2753883.563892.275
4893920.773920.77
4903932.993932.99
4913929.4953929.495
49239303930
4933922.9253922.925
4943930.0053930.005
4953935.3453935.345
4963929.4953929.495
4973927.3153927.315
4983932.583932.58
4993930.443930.44
5003935.5353935.535
5013945.4953945.495
5023934.7753934.775
5033940.4253940.425
5043946.063946.06
50539503950
5063950.493950.49
5073944.993944.99
5083938.1653938.165
5093933.0953933.095
5103932.9353932.935
5113931.883931.88
5123927.4353927.435
5133928.0153928.015
5143931.0053931.005
5153926.6353932.0853926.6353932.085
51639253933.41539253933.415
5173920.66539303920.6653930
5183925.8953928.6053925.8953928.605
5193926.9653929.9753926.9653929.975
5203922.5153931.653922.5153931.65
Sheet1




EG 2
I had a data set with zeros so I tried both to hide them for formatting purposes & replace them with blank cells & in both instances it returned results in 2nd column where all subsequent values return in bold. This isn’t an issue (just curious observation that may relate to EG 3)
Missing Values JoMoe.xlsm
AB
13771.863803.425
23774.063798.295
33765.0153790
43755.53783.495
53759.953779.35
63775.583792.325
73797.87
83808.095
93815
103811.375
113809.765
123808.005
133811.01
143813.45
153816.17
163808.995
173811.41
183820.17
193831.98
203826.145
213822.66
223819.06
233817.085
243817.085
253817.46
263816
273805.225
283805.575
293810.145
303814.82
313820
323823.92
333824.98
343819.8
353820.26
363825.07
373830.165
383831.065
393835.05
403836.54
413841.21
423830.053839.19
433828.2453838.1
443827.4653834.585
453824.293836.29
463820.1153828.94
473825.3553828.05
4838263834.575
493812.733832.92
503812.653827.655
513813.2553821.275
523808.2653821.615
533801.9453816.66
543806.3053812.76
553809.7553817.985
563816.4553820.545
573816.873822.47
5838203823.6
593817.53825
603819.143821.905
613813.9753822.7
6238103822.37
633807.973819.48
643806.543816.66
653810.083814.26
663813.7653817.49
673809.553816.99
683807.993816.83
693807.143814.32
703809.343816.475
713815.9553828.54
723826.6853834.425
733828.133838.955
7438313844.25
753839.2353847.875
7638443849.14
773848.2553858.125
7838503863.5
793850.0053858.275
803846.5853855.29
813837.4853853.495
823840.093849
833840.413848.25
843847.5653850.7
853848.2153852.585
863842.83851.74
873840.53849.985
883844.493856.63
893852.243857.44
903853.2153863.01
913856.553865.615
923852.53868.69
9338503864.27
943839.1953856.92
953846.6653856.15
963853.353860
9738503860
983847.5153859.995
993851.483859.4
1003854.433862.5
1013858.6653860.845
1023856.343866.515
1033856.6853863.145
1043856.573863.48
1053849.943864.935
10638443857.125
1073848.713852.12
1083844.0053855
1093839.1853854.995
1103841.43851.895
11138413847.515
1123846.3053851.2
1133847.5053856.42
1143848.5253856.42
1153843.53856.905
1163841.53851.845
1173835.013849.09
1183834.9853845.705
1193837.8053844.995
1203840.8553845
1213823.4753848.49
1223820.2653842.675
1233810.823830.36
1243816.413824.5
1253817.933827.525
1263819.7453827.6
1273820.53829.47
1283816.2353829.73
1293815.0053828.75
1303813.0453822.285
13137983821.115
1323805.53818.935
1333808.8253823.36
1343822.5153831.03
1353827.7053848.015
1363843.2253894.455
1373893.5053957.91
13839183972.2
1393957.905
1403966.46
1413973.49
1423974.5
1434008.185
1444010
1454009.825
1463994.2
1473988.755
1483976.825
1493983.305
1503985.685
1513985.965
1523969.75
1533972.035
1543975.25
1553977
1563978.585
1573978.99
1583980.87
1593978.5
1603962.32
1613962.06
1623963.235
1633959.635
1643954.865
1653950.86
1663951.05
1673955.01
1683962.525
1693966.665
1703962.955
1713956.13
1723965.44
1733968.07
1743967.365
1753964.015
1763960.565
1773964.21
1783968
1793965.91
1803962.06
1813961.45
1823959.545
1833957.02
1843947.355
1853945.575
1863948.885
1873949.965
1883951.82
1893952.895
1903957.225
1913958.495
1923959.95
1933972.96
1943975
1953980.865
1963966.67
1973967.5
1983965.715
1993958.955
2003961.275
2013955.165
2023956.595
2033961.29
2043962.64
2053963.555
2063960
2073957.5
2083958.525
2093951.255
2103952.3
2113953.56
2123956.48
2133959.675
2143956.855
2153957.665
2163955.485
2173952.75
2183955.915
2193959.995
2203963.38
2213966.615
2223969.28
2233968.77
2243965
2253961.86
2263961.99
2273962.24
2283963.13
2293966.98
2303966.98
2313967
2323966.5
2333965.625
2343974.935
2353981.65
2363986.58
2373991.425
2383990.08
2393984.955
2403986.25
2413989.735
2423987
2433984.275
2443980.94
2453972.5
2463964
2473956.56
2483951.595
2493948.795
2503956.185
2513962.41
2523957.045
2533954.425
2543952.42
2553952.435
2563957.1
2573950.46
2583954.025
2593960.625
2603960.625
2613960.975
2623960.885
2633964.5
2643966.405
2653972.5
2663972.74
2673975
2683972.225
2693970.89
2703965.98
2713965.15
2723965.735
2733971.635
2743971.635
2753976.46
2763967.435
2773968.59
2783969.435
2793974.5
2803978.36
2813987.67
2823987.5
2833988
2843988.495
2853984.44
2863991.375
2873996.3
2884000
2894005
2903991.925
2913978.825
2923979.825
2933978.985
2943978.5
2953978.545
2963978.5
2973974.66
2983980.825
2993980.825
3003983.63
3013982.43
3023976.67
3033974.15
3043974.495
3053974.5
3063970
3073969.995
3083969.995
3093967
3103968
3113971.455
3123973.145
3133971.97
3143972
3153971.39
3163967.19
3173967.065
3183967.86
3193968.33
3203965.17
3213958.425
3223960.67
3233963
3243964.125
3253966.5
3263964.615
3273960.945
3283955.47
3293961.17
3303962.345
3313963.24
3323966.32
3333967.42
3343967.555
3353967.04
3363967.495
3373968.25
3383967.39
3393970.485
3403970.08
3413969.885
3423969
3433967.935
3443963.185
3453964.28
3463964.095
3473962.41
3483958.775
3493961.375
3503959.37
3513959.75
3523954.865
3533956.105
3543960.45
3553966.28
3563966.525
3573966.02
3583965.53
3593963.605
3603960.915
3613956.22
3623957.255
3633956.665
3643955.08
3653950
3663949.83
3673953.25
3683950
3693949.415
3703952.045
3713948.465
3723945
3733944.365
3743937.885
3753940
3763947.515
3773950
3783950.255
3793950.305
3803954.845
3813954.845
3823958.35
3833954.5
3843950.04
3853947.5
3863948.615
3873949.96
3883955.77
3893956
3903957.705
3913959.025
3923959.275
3933961.77
3943961.98
3953961.665
3963965.365
3973968.04
3983965.94
3993957.28
4003956.915
4013954.975
4023957.53
4033958.485
4043960
4053956.4
4063953.58
4073951.5
4083949.555
4093952.505
4103943.765
4113894.805
4123900.995
4133902.11
4143896
4153896.58
4163899.215
4173897
4183899.5
4193899
4203902.5
4213893.5
4223896.54
4233899.075
4243897.99
4253896.94
4263861.53894.345
4273880.955
4283860.53880
4293853.4153875.085
4303884.635
4313884.66
4323865.133885
4333871.643885.01
4343875.133881.655
4353871.0853882.385
4363865.693877.46
4373868.253874.975
4383871.3253882.935
43938803883.215
4403883.0753891.025
4413892.97
4423881.063892.495
4433878.5653890.845
4443884.6253889.13
4453885.233894.495
4463894.495
4473882.5253895
4483881.693892.725
4493883.8353888.18
4503892.52
4513896.25
4523884.2353896.345
4533885.023892.875
4543892.5
4553892.5
4563875.7253900.35
4573865.53893.47
4583869.4953882.14
4593875.6153881.495
4603875.9153884.68
4613879.173884.68
4623879.8353885.175
4633879.4253886.17
4643885.393888.075
46538853892.42
4663880.5453890.385
4673882.473887.37
4683885.6553888.885
46938853890.615
4703878.1453892.45
4713872.53890.95
4723868.933882.485
4733872.53880.225
47438653879.82
4753865.5153874.82
4763868.33878.44
4773877.453882.165
4783879.673883.325
4793880.253887.88
4803880.183888.435
4813867.723888.365
4823846.1153883.115
4833860.033869.155
48438603872.89
4853863.293869.995
48638653884.78
4873880.3053888.335
4883883.563892.275
4893920.77
4903932.99
4913929.495
4923930
4933922.925
4943930.005
4953935.345
4963929.495
4973927.315
4983932.58
4993930.44
5003935.535
5013945.495
5023934.775
5033940.425
5043946.06
5053950
5063950.49
5073944.99
5083938.165
5093933.095
5103932.935
5113931.88
5123927.435
5133928.015
5143931.005
5153926.6353932.085
51639253933.415
5173920.6653930
5183925.8953928.605
5193926.9653929.975
5203922.5153931.65
5213917.53927.605
5223918.7453924.5
5233922.813924.845
5243923.8053930.37
5253930.3653935.5
5263932.6453936.635
5273933.9453957.12
5283973.99
5293959.45
5303932.7753957.73
5313945.41
5323948.66
5333949.5
5343932.763953.795
5353928.0553944.245
5363945
5373952.33
5383955
5393956.005
5403956.5
5413957.445
5423956.35
5433955.74
5443955.485
5453956.7
5463959.225
5473960.44
5483959.235
5493948.945
5503946.535
5513944.435
5523945.425
5533947.575
5543948.9
5553952.5
5563952.52
5573954.645
5583953.26
5593951.5
5603952.86
5613944.715
5623931.7453943.48
5633933.63948.07
5643964.89
5653951.065
5663947.36
5673952.985
5683952.985
5693953.425
5703954.1
5713952.16
5723947.485
5733933.1753947.505
5743942.45
5753946.455
5763946.5
5773930.4853948.32
5783910.753941.605
5793918.033932.465
5803922.553929.66
5813915.053925.73
5823925.5453930.59
5833928.0453931
5843920.0353931.06
5853906.863928.95
5863894.4953920.7
5873901.033915.295
5883905.773909.075
5893902.7753913.245
5903909.213914.26
5913910.193916.315
59239113916.485
5933912.5353916.535
5943913.5353918.85
5953912.3653923.185
5963917.863923.965
5973913.5353924.19
5983908.6353921.755
5993908.053917.525
6003914.263920
6013917.5053921.065
6023915.013921.3
60339153921.425
60439153920.25
6053919.253925.91
6063923.9753930.6
6073931.1453954.325
6083992.8
6094000.235
6104020
6114016.92
6124022.5
6134018.82
6144005.515
6154000
6163987.22
6173990.725
6183987.79
6193983.66
6203979.31
6213977.385
6223979.88
6233982.485
6243985.995
6253985.42
6263980.105
6273984.19
6283985.65
6293984.55
6303980.975
6313975.49
6323973.715
6333972.5
6343975.105
6353976.915
6363976.775
6373972.905
6383969.265
6393970
6403968.095
6413965
6423965
6433962.735
6443968.83
6453964.175
6463966.475
6473965
6483965.505
6493958.67
6503961.365
6513962.69
6523965
6533965
6543965
6553965
6563966.495
6573967.555
6583968.43
6593967.77
6603964.285
6613963.99
6623956.483962.175
6633955.0053962.5
6643955.883961.925
6653959.0953969.015
6663968.9953971.955
6673970.7753980
6683971.1753982.17
6693979.013987.23
67039793986.81
6713972.1653985.125
6723970.623980.02
6733965.793978.145
67439653977.45
6753965.1553974.645
67639613972.405
6773962.073968
67839613968.435
6793965.8253968
6803967.443972.34
68139683972.015
6823967.5153972.01
6833943.7053972.27
6843930.53970.54
6853928.3653948.625
6863936.633945.24
6873938.0153948.995
6883947.793950.5
6893946.5853953.13
6903937.513951.91
6913947.5353956.86
69239553964.92
6933960.0853965.785
6943960.533966.9
6953960.283973.175
6963971.53993.155
6973984.213995.22
6983978.954006
6993976.063986.145
7003969.763984.69
7013968.0153978.34
7023971.153976.975
7033972.53976.505
7043965.93980.365
7053966.323976.625
7063971.413977.58
7073970.8353979.75
7083971.9653979.505
7093969.123977
7103967.8153976.67
7113968.8253976.705
7123972.953977.215
7133973.8953980.5
7143979.443986.935
7153978.5153988.43
7163986.5253995.565
71739853997.595
7183978.3453997.07
7193978.9353988.61
7203987.883997.035
7213986.7354001.5
7223991.954005.87
7233989.114010
7243982.54007.51
7253986.5253994.225
7263975.53996.91
7273976.023992.795
7283978.33986.93
7293981.513985.65
7303978.3953988.895
7313986.853992.845
7323990.2653995.075
7333983.983995.155
73439793996.53
7353980.033986.375
7363979.163984.98
7373972.53983.465
7383974.3753983.97
7393981.53986.155
7403983.5253992.745
7413990.2853996.81
7423988.3953995
7433979.0053993.025
74439803991.9
7453975.5353989.075
7463954.953983.955
7473963.0153975.975
7483966.5453977.005
7493975.4153989
7503986.0353993.305
7513985.54002.5
7523982.54001.82
7533983.9153996.36
7543985.1053994.945
7553987.53996.115
7563988.3554001.565
7574001.5754066.815
Sheet1


EG 3
This following error I ran into a number of times over a few data sets. After attempting to run VBA code got an error 13 runtime error. After a little investigation it seemed to resolve if I replaced all the blank cells with blank cells from a file that did run without error 13 – strange. On most occasions this was a mixed variable issue, with either the blanks cells being ‘general’ or ‘number’ when the value cells were the opposite. However if I converted all cells to either error 13 still occurred.
Jomoe Test 2.25 Todayiii.xlsm
CD
13690.83707.248
23680.83701.74
33620.43695.644
43658.5283682.852
53657.5683674.192
63663.4683671.1
73668.9683679.104
83655.4123681.396
936563676.472
103645.6643674.148
113653.1163666.408
123660.9683668
133665.2483675.324
143673.2883678.232
153670.6963679.412
163672.6643679.18
173673.5283680
183676.5683680
193677.4723686.656
203684.7923692.088
213687.23691.496
223688.5363694.632
233689.0883695.528
243685.2083697.764
253684.7923698.492
263686.3483694.584
273691.4883697.24
283693.0123697.344
293689.2843704.332
303690.43702.46
313702.856
323704.292
333693.5123703.028
343693.083707.6
353687.1123704.936
3636883697.616
373692.6443696.58
383688.9243699.84
393700.156
403702.144
413703.996
423716.908
433711.428
443718.804
453723.868
463723.876
473727.764
483731.396
493736.296
5036883731.396
513691.1163718.784
523712.352
533711.28
543730.872
553730.872
563735.344
573732.328
583722.688
593724.396
603723.996
613719.8
623715.056
633663.6323704.604
643693.8243709.032
653712
6636863708.8
6736883699.96
683675.6363697.2
693673.5763693.34
703686.0123693.6
713693.6883698.992
723702.252
733688.0043700.304
743689.943697.452
753691.0683700.864
763689.7683701.636
Sheet1


Interestingly using Eric W CF update but replacing +10
=IF(B2>MIN(INDEX(A:A,LOOKUP(2,1/C$1:C1,ROW(C$1:C1)+1)):A2)+10,1,"")
for *1.0225
=IF(B2>MIN(INDEX(A:A,LOOKUP(2,1/C$1:C1,ROW(C$1:C1)+1)):A2)*1.0225,1,"")
seemed to achieve the objective.
 
Upvote 0
In the case of your EG1 the failure to make bold D143 is because I took you literally when you said ignore all blanks in col A (C in your example) thinking that included not making col B (D in your example) companion cells bold if they otherwise met the test for bold. The revision below now boldens any col B cell that meets the test regardless of whether its col A companion is blank or not.

For EG2 and EG3, I can't reproduce your results, making diagnosis difficult. Are your data imported from some other application?
VBA Code:
Sub HiliteValuesDescending5()
Const Delta As Double = 10  'the amount by which col B must exceed col A to bolden col B entry
Const Percent As Double = 1.0225 ' Col B must exceed col A by (Percent x col A) to bolden col B entry
Dim Ra As Range, Va As Variant, Rb As Range, Vb As Variant, i As Long, Mn As Variant
Dim Rfill As Range, Rbold As Range, Ans As String
Ans = InputBox("Which constant do you want to use: Delta or Percent?")
If Ans = "" Then Exit Sub
If Ans <> "Delta" And Ans <> "Percent" Then
    MsgBox "You must enter either Delta or Percent - try again."
    Exit Sub
End If
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) >= IIf(Ans = "Delta", Mn + Delta, Percent * Mn) Then
    Set Rbold = Range("B1")
    If Not IsEmpty(Va(2, 1)) Then
        Set Rfill = Union(Rfill, Ra(2))
        Mn = Va(2, 1)
    End If
End If
For i = LBound(Va, 1) + 1 To UBound(Va, 1) - 1
    If Not IsEmpty(Va(i, 1)) Then
        If Va(i, 1) < Mn Then
            Mn = Va(i, 1)
            Set Rfill = Union(Rfill, Ra(i))
        End If
    End If
    If Vb(i, 1) >= IIf(Ans = "Delta", Mn + Delta, Percent * Mn) Then
        If Rbold Is Nothing Then
            Set Rbold = Rb(i)
        Else
            Set Rbold = Union(Rbold, Rb(i))
        End If
        If Not IsEmpty(Va(i + 1, 1)) Then
            Set Rfill = Union(Rfill, Ra(i + 1))
            Mn = Va(i + 1, 1)
        End If
    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) >= IIf(Ans = "Delta", Mn + Delta, Percent * Mn) 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
 
Upvote 0
Hey Joemo, So VBA code is so close now I can smell it. I think the only issue remaining is that some of the desired reset values in first column are not highlighting (in yellow). Again I believe this is an explanation oversight on my side. And is best explained by a correction to my original explanation.

In relation to "Restart rule". So overall this is what I want:
1) Instances of Col C being at least ‘10’ higher than Col A [Or updated: 'A defined percentage above']
In the above instance want Start Value in Col A to be highlighted via Cell Fill / Bold & corresponding Col C matching the ('defined') condition highlighted too.

2) And I need the ‘reset’ conditions for the sequentially lower rule in COL A to be each time the above (condition) occurs. i.e from next New Cell on Col A. (after condition occurs)
CORRECTION : Reset rule starts immediately after condition occurs. So that no further values are boldened in Col B and next occurrence in Col A whatever the value resets the sequentially lower rule.

The below is an eg of it in theory. With Manual highlighting for cells that wouldn't be highlighted in red & cells that should be highlighted in YELLOW
25.6.20 eg4i.xlsm
ABCDE
13125.4883143.163125.4883143.16
23115.5963136.563115.5963136.56
33120.8243132.2363120.8243132.236
43124.6163127.263124.6163127.26
53122.223130.5963122.223130.596
63127.3683131.4083127.3683131.408
73128.1523133.0523128.1523133.052
83128.83133.1883128.83133.188
93130.0283133.2283130.0283133.228
103130.8283135.083130.8283135.08
113129.8923138.5483129.8923138.548
123134.2883139.1723134.2883139.172
133130.8283139.3523130.8283139.352
143126.9083137.4043126.9083137.404
153126.443134.023126.443134.02
163131.40831363131.4083136
173134.0043136.8523134.0043136.852
183132.0083137.043132.0083137.04
1931323137.1431323137.14
2031323136.231323136.2
213135.43140.7283135.43140.728
223139.183144.483139.183144.48
233144.9163163.463144.9163163.46
243194.243194.24
253200.1883200.188
2632163216
273213.5363213.536
2832183218
293215.0563215.056
303204.4123204.412
3132003200
323189.7763189.776
333192.583192.58
343190.2323190.232
353186.9283186.928
363183.4483183.448
373181.9083181.908
383183.9043183.904
393185.9883185.988
403188.7963188.796
413188.3363188.336
423184.0843184.084
433187.3523187.352
443188.523188.52
453187.643187.64
463184.783184.78
473180.3923180.392
483178.9723178.972
4931783178
503180.0843180.084
513181.5323181.532
523181.423181.42
533178.3243178.324
543175.4123175.412
5531763176
563174.4763174.476
5731723172
5831723172
593170.1883170.188
603175.0643175.064
613171.343171.34
623173.183173.18
6331723172
643172.4043172.404
653166.9363166.936
663169.0923169.092
673170.1523170.152
6831723172
6931723172
7031723172
7131723172
723173.1963173.196
733174.0443174.044
743174.7443174.744
753174.2163174.216
763171.4283171.428
773171.1923171.192
783165.1843169.743165.1843169.74
793164.00431703164.0043170
803164.7043169.543164.7043169.54
813167.2763175.2123167.2763175.212
823175.1963177.5643175.1963177.564
833176.6231843176.623184
843176.943185.7363176.943185.736
853183.2083189.7843183.2083189.784
863183.23189.4483183.23189.448
873177.7323188.13177.7323188.1
883176.4963184.0163176.4963184.016
893172.6323182.5163172.6323182.516
9031723181.9631723181.96
913172.1243179.7163172.1243179.716
923168.83177.9243168.83177.924
933169.6563174.43169.6563174.4
943168.83174.7483168.83174.748
953172.663174.43172.663174.4
963173.9523177.8723173.9523177.872
973174.43177.6123174.43177.612
983174.0123177.6083174.0123177.608
993154.9643177.8163154.9643177.816
1003144.43176.4323144.43176.432
1013142.6923158.93142.6923158.9
1023149.3043156.1923149.3043156.192
1033150.4123159.1963150.4123159.196
1043158.2323160.43158.2323160.4
1053157.2683162.5043157.2683162.504
1063150.0083161.5283150.0083161.528
1073158.0283165.4883158.0283165.488
10831643171.93631643171.936
1093168.0683172.6283168.0683172.628
1103168.4243173.523168.4243173.52
1113168.2243178.543168.2243178.54
1123177.23194.5243177.23194.524
1133187.3683196.1763187.3683196.176
1143183.163204.83183.163204.8
1153180.8483188.9163180.8483188.916
1163175.8083187.7523175.8083187.752
1173174.4123182.6723174.4123182.672
1183176.923181.583176.923181.58
11931783181.20431783181.204
1203172.723184.2923172.723184.292
1213173.0563181.33173.0563181.3
1223177.1283182.0643177.1283182.064
1233176.6683183.83176.6683183.8
1243177.5723183.6043177.5723183.604
1253175.2963181.63175.2963181.6
1263174.2523181.3363174.2523181.336
1273175.063181.3643175.063181.364
1283178.363181.7723178.363181.772
1293179.1163184.43179.1163184.4
1303183.5523189.5483183.5523189.548
1313182.8123190.7443182.8123190.744
1323189.223196.4523189.223196.452
13331883198.07631883198.076
1343182.6763197.6563182.6763197.656
1353183.1483190.8883183.1483190.888
1363190.3043197.6283190.3043197.628
1373189.3883201.23189.3883201.2
1383193.563204.6963193.563204.696
1393191.28832083191.2883208
14031863206.00831863206.008
1413189.223195.383189.223195.38
1423180.43197.5283180.43197.528
1433180.8163194.2363180.8163194.236
1443182.643189.5443182.643189.544
1453185.2083188.523185.2083188.52
1463182.7163191.1163182.7163191.116
1473189.483194.2763189.483194.276
1483192.2123196.063192.2123196.06
1493187.1843196.1243187.1843196.124
1503183.23197.2243183.23197.224
1513184.0243189.13184.0243189.1
1523183.3283187.9843183.3283187.984
15331783186.77231783186.772
1543179.53187.1763179.53187.176
1553185.23188.9243185.23188.924
1563186.823194.1963186.823194.196
1573192.2283197.4483192.2283197.448
1583190.71631963190.7163196
1593183.2043194.423183.2043194.42
16031843193.5231843193.52
1613180.4283191.263180.4283191.26
1623163.963187.1643163.963187.164
1633170.4123180.783170.4123180.78
1643173.2363181.6043173.2363181.604
1653180.3323191.23180.3323191.2
1663188.8283194.6443188.8283194.644
1673188.432023188.43202
16831863201.45631863201.456
1693187.1323197.0883187.1323197.088
1703188.0843195.9563188.0843195.956
17131903196.89231903196.892
1723190.6843201.2523190.6843201.252
1733201.263253.4523201.263253.452
Sheet1


REF Cells in range E32 to E52 (representing Cell between D32 to D52 )
Cell A85 should therefore be highlighted as the new reset point ie the very next cell following condition being met.
This would also mean Cell B91 not boldened & Cells A92 - 97 / 99 not highlighted
Sorry for confusion & the fact this has meant wasting your efforts......
For EG2 and EG3, I can't reproduce your results, making diagnosis difficult. Are your data imported from some other application?
It did seem to be a formatting import error which I have resolved. Thanks for looking into it though.

Phew then I think we are there. Brimming with gratitude for your time & patience (if indeed you have any left).
 
Upvote 0
Thanks for clarifying the reset rule, it is different from what I had gleaned from your prior posts. Please help me further by answering the following:
1. In the example data you show in Post #19, are you using Delta or Percent and what is the value for the one you are using?
2. Why is cell A79 not highlighted if A78 is the reset cell? A79 is less than A78.
Also, can you do careful manual highlighting and boldening for the data set w/o the red fill and post it using XL2BB - it's very helpful for me.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,788
Members
448,297
Latest member
carmadgar

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
Back
Top