Excel: how to highlight the minimum value(cell) within every 3 rows?

0
votes

I need to highlight all the minimum valued cells within every 3 rows. I tried using the conditional formatting. This is the formula i typed in:

Image

enter image description here

As seen in the image, i tried to have two different formulas for the entire cell; first formula for defining every three rows, and the second formula for highlighting the minimum value within the three rows.

But this does not seem to work. Is there any better way to tackle this problem?

2
excelexcel-formulaexcel-2010
I'd use a helper column next to it that compares and returns a "Max" if it's the max value of the three, then conditional format against that cell. - Andreas
The range from $C$352:$AH$1215 includes many rows and columns. Are you looking for a minimum value across 3 rows and several columns or just one column at a time? As always, a mock-up of some of the data and expected results would be helpful. - Tom Sharpe

2 Answers

1
votes

If I understand your question right, then this could be achieved using OFFSET for creating ranges of 3 rows each.

Example:

enter image description here

Formula in G2 downwards:

=INT((ROW(A2)-ROW($A$2))/3)*3

Formula in I2 downwards:

=MIN(OFFSET($A$2,INT((ROW(A2)-ROW($A$2))/3)*3,0,3,COLUMNS($A:$E)))

Note, those formulas are only to understand how the approach works. They are not helper columns and are not needed for the conditional formatting.

Conditional formatting formula applied to range =$A$2:$E$13 would then be:

=(A2=MIN(OFFSET($A$2,INT((ROW(A2)-ROW($A$2))/3)*3,0,3,COLUMNS($A:$E))))
0
votes

You can try: 

Option Explicit

Sub test()

    Dim i As Long, min As Long, LR As Long
    Dim rng As Range, cell As Range


    With ThisWorkbook.Worksheets("Sheet1")

        LR = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 1 To LR Step 3

            Set rng = .Range("A" & i & ":A" & i + 2)
            min = Application.WorksheetFunction.min(rng)

            For Each cell In rng
                If cell.Value = min Then
                    cell.Interior.Color = vbGreen
                    Exit For
                End If
            Next cell

        Next i
    End With

End Sub