Highlight Min Separate Ranges

B

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
Good evening

Is there a way to highlight min value in separate ranges.

I am utilizing some code provided from an earlier thread. Could I use the following repeated?

The code is

[TABLE="width: 70"]
<colgroup><col width="70" style="width:53pt"> </colgroup><tbody>[TR]
[TD="width: 70"]Sub FormatMin()[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] Dim Col As Integer[/TD]
[/TR]
[TR]
[TD] Dim Row As Integer[/TD]
[/TR]
[TR]
[TD] Dim Row2 as Integer[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] For Col = 1 To 11[/TD]
[/TR]
[TR]
[TD] For Row = 1 To 8[/TD]
[/TR]
[TR]
[TD]For Row2 = 9 to 18[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] If Cells(Row, Col) = WorksheetFunction.Small(Columns(Col), 1) Then[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] Cells(Row, Col).Font.Bold = True[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] Next Row[/TD]
[/TR]
[TR]
[TD] Next Col[/TD]
[/TR]
[TR]
[TD="class: xl90"]Next Range[/TD]
[/TR]
[TR]
[TD]If cells(Row2,Col) =WorksheetFunction.Small(Columns(Col),1)Then[/TD]
[/TR]
[TR]
[TD]Cells(Row2,Col).Font.Bold = True[/TD]
[/TR]
[TR]
[TD]End If[/TD]
[/TR]
[TR]
[TD]Next Row2[/TD]
[/TR]
[TR]
[TD]Next Col[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
What are your ranges and could you possibly use Conditional Formatting?
 
Upvote 0
Hoping the table below will further explain what I am trying to attain.


Highlight the lowest value in each column in each range. The rows are as above 1 to 8, 9 to 18 .....



[TABLE="width: 216"]
<tbody>[TR]
[TD="class: xl70, width: 72, bgcolor: transparent"]Col A
[/TD]
[TD="class: xl70, width: 72, bgcolor: transparent"]Col B
[/TD]
[TD="class: xl70, width: 72, bgcolor: transparent"]Col C
[/TD]
[TD="class: xl70, width: 72, bgcolor: transparent"]Col D
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]0.7868
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0.9083
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0.7446
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0.8824
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.78
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.8701
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7582
[/TD]
[TD="class: xl67, align: right"]0.3124
[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]0.7398
[/TD]
[TD="class: xl67, align: right"]0.8572
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6907
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.4139
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7795
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.9291
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7497
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6398
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.8615
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.9747
[/TD]
[TD="class: xl67, align: right"]0.5236
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.8205
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7692
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.9457
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7297
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6462
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]0.7796
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.9343
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.707
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]0.7855
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6701
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.8649
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6121
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.5403
[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]0.331
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7917
[/TD]
[TD="class: xl67, align: right"]0.4843
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.614
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6474
[/TD]
[TD="class: xl67, align: right"]0.2214
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6275
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7272
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7692
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.9457
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7297
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6462
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7796
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.9343
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.707
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7855
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6701
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.8649
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.6121
[/TD]
[TD="class: xl67, align: right"]0.3214
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0.4412
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.7912
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.5423
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.614
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: right"]0.6474
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]0.8673
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]0.6275
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]0.7272
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG31Jan25
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] ac = 1 To 11
    Rw = Array(1, 8, 8, 18, 19, 28, 29, 38, 39, 41)
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Rw) [COLOR="Navy"]Step[/COLOR] 2
            [COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(Rw(n), ac), Cells(Rw(n + 1), ac))
    
    [COLOR="Navy"]Set[/COLOR] R = Rng(1)
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Dn.Value < R.Value [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] R = Dn
        [COLOR="Navy"]Next[/COLOR] Dn
        R.Interior.Color = vbYellow
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
The ranges rows "Rw" are defined by the array:-
(as Described in your original data/thread)

"Rw = Array(1, 8, 8, 18, 19, 28, 29, 38, 39, 41)"

and the columns by the start of loop as below

"For ac = 1 To 11"

If you alter these to your required ranges /columns it should still work !!!
Regrds Mick
 
Last edited:
Upvote 0
Changed the ranges. Works great. I have a couple of questions if you will.

Do arrays need to be declared?

Unsure what the for loop 0 to unbound is doing? Is the Dn value looping through all the cells in the array column range to find the minimum value?


Thank You
 
Upvote 0
You must log in or register to reply here.

Similar threads

montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
137
montecarlo2012
montecarlo2012
E
  • Solved
Delete Row if
Replies
6
Views
191
Eric Penfold
E
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
443
rahildhody
R
H
  • Question Question
Macro to Autofit Cols Page Preview
Replies
6
Views
135
Logit
Logit
I
  • Question Question
Unable to run VBA code
Replies
6
Views
165
RoryA
RoryA

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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