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]

I have tried different loops but it says "For control variable already in use".

Could you give me some help with the nested loop?

 

[Fluff]

Maybe something like

Public Sub ColourSomeCells()

   Dim iRow As Long, iCol As Long, iRow2 As Long
   Dim TotalRows As Long
   Dim ws As Worksheet
   Dim ColourColumns As Variant
   Dim LastRowInColumn As Long
   Dim MyMax As Double, MyMin As Double
   
   Set ws = ThisWorkbook.Worksheets("PoängTest") 'define the worksheet
   Application.ScreenUpdating = False
   
   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 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 Step numRows
         With ws.Range(ws.Cells(iRow, iCol), ws.Cells(iRow + 39, iCol))
            MyMax = Application.Max(.Value)
            MyMin = Application.Min(.Value)
         End With
         For iRow2 = 1 To 40
            With ws.Cells(iRow2, iCol)
               Select Case .Value
                  Case MyMax
                     .Interior.Color = RGB(255, 199, 206)
                  Case MyMin
                     .Interior.Color = RGB(255, 199, 6)
               End Select
            End With
         Next iRow2
      Next iRow
   Next iCol 'next column
End Sub

 

[Waimea]

Thank you for your reply Fluff! I get two error messages,

"For each control variable must be variant or object"

and when I define iCol As Variant instead of iCol As Long.

I get a runtime error 13: type mismatch:

For Each iCol In ColourColumns 'loop through the array of columns

I am not good enough at VBA to figure out how to fix this?

 

[Waimea]

I got it to work by using a piece of old code.

ColourColumns = Array("I", "J", "K") 'columns you want to color

I guess the type mismatch came from the InputBox?

 

[Fluff]

Oops
iCol should be variant & the loop should be

   For Each iCol In Split(ColourColumns, ",") 'loop through the array of columns

 

[Waimea]

I understand most of your code but when I run the code it only loops through the first 40 rows and colors the min and the max values.

I want to loop through 1600 rows in batches of 40.

What am I doing wrong?

 

[Fluff]

What am I doing wrong?

Absolutely nothing, it's my code

Try

Public Sub ColourSomeCells()

   Dim iRow As Long, iCol As Variant, iRow2 As Long
   Dim NumRows As Long
   Dim ws As Worksheet
   Dim ColourColumns As Variant
   Dim LastRowInColumn As Long
   Dim MyMax As Double, MyMin As Double
   
   Set ws = ThisWorkbook.Worksheets("pcode") 'define the worksheet
   Application.ScreenUpdating = False
   
   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 Each iCol In Split(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 Step NumRows
         With ws.Range(ws.Cells(iRow, iCol), ws.Cells(iRow + NumRows - 1, iCol))
            MyMax = Application.Max(.Value)
            MyMin = Application.Min(.Value)
         End With
         For iRow2 = iRow To iRow + NumRows - 1
            With ws.Cells(iRow2, iCol)
               Select Case .Value
                  Case MyMax
                     .Interior.Color = RGB(255, 199, 206)
                  Case MyMin
                     .Interior.Color = RGB(255, 199, 6)
               End Select
            End With
         Next iRow2
      Next iRow
   Next iCol 'next column
End Sub

 

[Waimea]

Hi Fluff, thank you very much for your updated code.

Now it does exactly what I want.

 

[Fluff]

You're welcome & thanks for the feedback