Loop through 40 cells at a time and finding the highest value in that range

[Waimea]

I am using the following code to loop through all rows in the designated columns in the ColourColumns array and color the cells depending on their value with a select case statement.

I would like to loop with 40 rows i 1 column F, then the next 40 rows in the same column and compare the values in the active column and find the min and the max value and perhaps rank the values from largest to smallest.

Then loop through the next 40 rows in the same column until the last row, then I want to loop through the next 40 rows in column G, then columns H, I, J, K, N, and M.

I would like help with how to compare the rows in batches of 40 and finding the max/min value out of the 40 rows that are being iterated at that time?

Public Sub ColourSomeCells()

Dim i As Long
Dim TotalRows As Long
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("PoängTest") 'define the worksheet
    
    Dim ColourColumns As Variant
    ColourColumns = Array("F", "G", "H", "I", "J", "K", "N", "M") 'columns you want to color


    Dim LastRowInColumn As Long
    Dim iRow As Long


    Dim iCol As Variant
    
    For Each iCol In ColourColumns 'loop through the array of columns
        LastRowInColumn = ws.Cells(ws.Rows.Count, iCol).End(xlUp).Row 'find last used row in that column


        For iRow = 2 To LastRowInColumn 'loop through all used cells in that column
            With ws.Cells(iRow, iCol)
                Select Case .Value
                    Case 0 'make zeros red
                        .Interior.Color = RGB(255, 199, 206)
                        
                        Case 1, 2 'make zeros red
                        .Interior.Color = RGB(255, 199, 6)
                        
                        Case 3, 4 'make zeros red
                        .Interior.Color = RGB(55, 99, 206)

                        Case 5, 6 'make zeros red
                        .Interior.Color = RGB(55, 99, 206)
                        
                        Case 10, 11, 12 '
                        .Interior.Color = vbGreen
                        .Borders.Color = RGB(255, 255, 0)

                        Case Is > 20
                        .Interior.Color = vbBlue
                        
                        Case Is > 30 
                        .Interior.Color = RGB(185, 174, 165)
                        
                        Case Is > 40
                        .Interior.Color = RGB(85, 174, 65)


                        Case Is > 50
                        .Interior.Color = RGB(185, 74, 165)


                    Case Else


                End Select
            End With
        Next iRow 'next row in that column
    Next iCol 'next column
       
End Sub

 

[Waimea]

Anyone? How can I iterate through 40 rows at the time?

 

[Fluff]

Something like

        For iRow = 2 To LastRowInColumn Step 40
            With ws.Range(ws.Cells(iRow, iCol), ws.Cells(iRow + 39, iCol))
               MsgBox Application.Max(.Value)
               MsgBox Application.Min(.Value)
            End With
        Next iRow 'next row in that column

 

[Waimea]

Hi Fluff,

thank you for your answer! I will try it at once!

 

[Waimea]

If you start from scratch and ignore my earlier code, how would you go about looping through 40 or x cells at a time?

 

[Fluff]

Depends on what your data is like & what you are trying to do.

 

[Waimea]

Hi Fluff,

I have a worksheet with a table in it,

the table has dynamic number of columns and rows so I am trying to iterate through X cells at a time.

The first part of my data has the data arranged in rows of 40, my second data table has the data arranged in rows of 59.


I can't upload a workbook but the data looks like this:

[B]City name     Metric1     Metric2       Metric3     ...     Metric X     County[/B]

City1 City1         0             1                2                       12       County1
City1 City2         1             2                4                       5         County1
City1 City3         1             2                4                       5         County1
City1 City4         1             2                4                       5         County1
City1 City5         1             2                4                       5         County1
City1 City6         1             2                4                       5         County1
City1 City7         1             2                4                       5         County1
City1 City8         1             2                4                       5         County1
City1 City9         1             2                4                       5         County1
City1 City10       1             2                4                       5         County1

... 

City1 City40      5               1               1                         4      County1    


Then it starts over:


City2 City1         0             1                2                       12       County1
City2 City2         1             2                4                       5         County1
City2 City3         1             2                4                       5         County1
City2 City4         1             2                4                       5         County1
City2 City5         1             2                4                       5         County1
City2 City6         1             2                4                       5         County1
City2 City7         1             2                4                       5         County1
City2 City8         1             2                4                       5         County1
City2 City9         1             2                4                       5         County1
City2 City10       1             2                4                       5         County1

... 

City2 City40      5               1               1                         4      County1    


...

All the way down to:
City40 City1         0             1                2                       12       County1
City40 City2         1             2                4                       5         County1
City40 City3         1             2                4                       5         County1
City40 City4         1             2                4                       5         County1
City40 City5         1             2                4                       5         County1
City40 City6         1             2                4                       5         County1
City40 City7         1             2                4                       5         County1
City40 City8         1             2                4                       5         County1
City40 City9         1             2                4                       5         County1
City40 City10       1             2                4                       5         County1

... 

City40 City40      5               1               1                         4      County1

I am trying to loop through all these possible combinations and find the highest values in each row of 40 and then color these values and sort on color.

I think that I want to loop through columns in an array where I decide which columns to iterate through and also decide the number of rows/batches, from 1 to 59 or X.

 

[Fluff]

You can set the columns & the number of rows per "batch" like

    ColourColumns = InputBox("Please enter columns to be checked in the format F,G,H")
    numRows = InputBox("Please enter the number of rows to be checked")

    For iRow = 2 To LastRowInColumn Step numRows

But to highlight individual cells you'll need to run yet another nested loop. Also what happens if you have multiple Max values as shown in your data

 

[Waimea]

That's is very clever with the InputBox, I didn't think about that.

How do you mean with another nested loop?

 

[Fluff]

You will have a loop with step 40 where you can find the max value in the col, but if you want to highlight it, then you will need to find where that value occurs within the 40 rows of data.