Trying to get cell address of min value with no luck using vba

[vba317]

I am trying to get the cell address of the min value in a range I am getting a Run-time error 424 Object required. The error is highlighted in yellow at the Set minCell line.

Any help is appreciated.

Sub LowHighUpdate1()


    Dim ws As Worksheet
    Dim wrksh As Excel.Worksheet
    Dim R1 As Long
    Dim LR As Long
    Dim minRng As Range
    Dim maxRange As Range
    Dim minCell As Range
    Dim maxCell As Range
    Dim lMin As Long
    Dim lMax As Long
    
    For Each wrksh In Sheets
        Select Case wrksh.Name
            Case "Parameters"
                GoTo Skip
            Case Else
                wrksh.Select
                    LR = wrksh.Range("A" & Rows.Count).End(xlUp).Row
                        For R1 = 7 To LR
                            Set minRng = Range("D" & R1 & ":D" & (R1 - 5))
                            lMin = Application.WorksheetFunction.Min(minRng)
               [YELLOW]  Set minCell = minRange.Find(minRng)   [/YELLOW]
                            Range(minCell).Interior.Color = vbGreen
                            'B leg High header
                            Set maxRange = Range("C" & R1 & ":C" & (R1 - 5))
                            lMax = Application.WorksheetFunction.Max(maxRng)
                            Range(maxCell).Interior.Color = vbRed
                        Next R1
                Columns("G:Z").NumberFormat = "0.00"
                wrksh.Columns("A:Z").EntireColumn.AutoFit
        End Select
Skip:
    Next wrksh
    Sheets("Parameters").Select
End Sub

 

[Eric W]

Change:

Set minCell = minRange.Find(minRng)

to

Set minCell = minRange.Find(lMin)

 

[Marcelo Branco]

Try

Sub LowHighUpdate1()
    Dim ws As Worksheet
    Dim wrksh As Excel.Worksheet
    Dim R1 As Long
    Dim LR As Long
  [COLOR=#ff0000]  Dim minRange As Range[/COLOR]
    Dim maxRange As Range
    Dim minCell As Range
    Dim maxCell As Range
    Dim lMin As Long
    Dim lMax As Long
    
    For Each wrksh In Sheets
        Select Case wrksh.Name
            Case "Parameters"
                GoTo Skip
            Case Else
                wrksh.Select
                    LR = wrksh.Range("A" & Rows.Count).End(xlUp).Row
                        For R1 = 7 To LR
                            Set minRange = Range("D" & R1 & ":D" & (R1 - 5))
                            lMin = Application.WorksheetFunction.Min(minRange)
[COLOR=#ff0000]                            Set minCell = minRange.Find(lMin)[/COLOR]
[COLOR=#ff0000]                            minCell.Interior.Color = vbGreen[/COLOR]
                            Set maxRange = Range("C" & R1 & ":C" & (R1 - 5))
                            lMax = Application.WorksheetFunction.Max(maxRange)
[COLOR=#ff0000]                            Set maxCell = maxRange.Find(lMax)[/COLOR]
[COLOR=#ff0000]                            maxCell.Interior.Color = vbRed[/COLOR]
                        Next R1
                Columns("G:Z").NumberFormat = "0.00"
                wrksh.Columns("A:Z").EntireColumn.AutoFit
        End Select
Skip:
    Next wrksh
    Sheets("Parameters").Select
End Sub

Hope this helps

M.

 

[vba317]

Thanks Eric and Marcelo Those sugestions worked great!

 

[vba317]

I have started running the following code and I am getting a run-time error 91 Object variable with block not set. This doesn't make sense to me because I am setting the variable. The code errors on the maxCell.Interior.Color = vbRed line. When I look at the lines before it
lMax found the correct max value for that range. But maxcell says it is set to nothing. Any help would be appreciated.

Sub LowHighUpdate1()


   Dim ws As Worksheet
    Dim wrksh As Excel.Worksheet
    Dim R1 As Long
    Dim LR As Long
    Dim minRange As Range
    Dim maxRange As Range
    Dim minCell As Range
    Dim maxCell As Range
    Dim lMin As Long
    Dim lMax As Long
    
    For Each wrksh In Sheets
        Select Case wrksh.Name
            Case "Parameters"
                GoTo Skip
            Case Else
                wrksh.Select
                    LR = wrksh.Range("A" & Rows.Count).End(xlUp).Row
                        For R1 = 8 To LR
                            Set minRange = Range("D" & (R1 - 5) & ":D" & R1)
                            lMin = Application.WorksheetFunction.Min(minRange)
                            Set minCell = minRange.Find(lMin)
                            minCell.Interior.Color = vbGreen
                            Set maxRange = Range("C" & (R1 - 5) & ":C" & R1)
                            lMax = Application.WorksheetFunction.Max(maxRange)
                            Set maxCell = maxRange.Find(lMax)
                            maxCell.Interior.Color = vbRed
                        Next R1
                Columns("G:Z").NumberFormat = "0.00"
                wrksh.Columns("A:Z").EntireColumn.AutoFit
        End Select
Skip:
    Next wrksh
    Sheets("Parameters").Select
 
 End Sub

 

[vba317]

I figured out the problem. I had to change lMin and lmax to variant. After that everything worked out great.