Could someone check out the VBA code here?
I would like someone to explain the reason why when the program is run, the maximum value in column S successfully is selected by being colored in pink, but the minimum in column R between those pink max's (indicated by the entire row outlined in red) is not the minimum.
In other words, look at the numbers in column R. Only the 14.08 rows are supposed to be boxed in red. I can't understand why the 30.08 and the 46.08 are boxed in red??? And puzzling to me is why the 46.08 near the top are boxed, yet the 46.08 near the bottom are not boxed.
Thank you!
Sub x()
Dim DstRng As Range
Dim R As Long
Dim RngEnd As Range
Dim rData As Range
Dim cell As Range
Dim dMin As Double
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("INPUT")
'Set the first row of the destination
Set DstRng = Range("A11:S11")
'Find any rows below the first that contain data
Set RngEnd = .Cells(Rows.Count, DstRng.Column).End(xlUp)
'Clear the cells
Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, .Range(DstRng, RngEnd))
DstRng.Clear
End With
With Worksheets("Details")
.UsedRange.Borders.LineStyle = xlNone
Set rData = .Range("R5", .Range("R604"))
For Each cell In rData
If cell.Offset(, 1).Value = WorksheetFunction.Max(rData.Offset(, 1)) Then
If dMin = 0 Or cell.Value <= dMin Then
dMin = cell.Value
With Intersect(.UsedRange, cell.EntireRow)
.BorderAround LineStyle:=xlContinuous, ColorIndex:=3, Weight:=xlMedium
.Copy
DstRng.Offset(R, 0).PasteSpecial xlPasteValues
R = R + 1
End With
End If
End If
Next cell
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False
End Sub
Bookmarks