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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
313
kvsrinivasamurthy
K
H
  • Solved
Double Click Cell to cell to Sheet Name and Cell
2
Replies
11
Views
811
howard
H
S
  • Question Question
Workbook Index of Sheet name Hyperlinks + additional information
Replies
1
Views
304
stuartgb100
S
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
400
maiwarits
M

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

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