Highlight Min Separate Ranges

[billandrew]

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

Sub FormatMin()

Dim Col As Integer

Dim Row As Integer

Dim Row2 as Integer

For Col = 1 To 11

For Row = 1 To 8

For Row2 = 9 to 18

If Cells(Row, Col) = WorksheetFunction.Small(Columns(Col), 1) Then

Cells(Row, Col).Font.Bold = True

End If

Next Row

Next Col

Next Range

If cells(Row2,Col) =WorksheetFunction.Small(Columns(Col),1)Then

Cells(Row2,Col).Font.Bold = True

End If

Next Row2

Next Col

End Sub

<colgroup><col width="70" style="width:53pt"> </colgroup><tbody>
</tbody>

 

[Scott Huish]

What are your ranges and could you possibly use Conditional Formatting?

 

[billandrew]

Prefer VBA.

Ranges are columns 1 to 11,

Rows
1 to 8,
9 to 18,
19 to 28,
29 to 38
39 to 41

 

[billandrew]

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 .....

Col A	Col B	Col C	Col D
0.7868	0.9083	0.7446	0.8824
0.78	0.8701	0.7582	0.3124
0.7398	0.8572	0.6907	0.4139
0.7795	0.9291	0.7497	0.6398
0.8615	0.9747	0.5236	0.8205
0.7692	0.9457	0.7297	0.6462
0.7796	0.9343	0.707	0.7855
0.6701	0.8649	0.6121	0.5403
0.331	0.7917	0.4843	0.614
0.6474	0.2214	0.6275	0.7272
0.7692	0.9457	0.7297	0.6462
0.7796	0.9343	0.707	0.7855
0.6701	0.8649	0.6121	0.3214
0.4412	0.7912	0.5423	0.614
0.6474	0.8673	0.6275	0.7272

<tbody>
</tbody>

 

[MickG]

Try this:-

[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

 

[billandrew]

Would you be able to provide an explanation in the event the ranges change

 

[MickG]

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

 

[billandrew]

Thank you. Appreciate the code and the explanation.

 

[MickG]

You're welcome

 

[billandrew]

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