Macros for highlighting max min values|VBA & Macros|Excel Forum|My Online Training Hub

Thanks for the query Phil. Unfortunately yes as part of a course assignment I have to use macro in order to show that macros are used. I tried using this

Dim zelle As Range

Dim rng As Range

Dim lCol As Long

Dim lLastRow As Long

  With ActiveSheet

    For lCol = e8 To .UsedRange.Columns.Count

      lLastRow = .Cells(.Rows.Count, lCol).End(xlUp).Row

      Set rng = .Range(.Cells(e8, lCol), .Cells(lLastRow, lCol))

      For Each zelle In rng

        If zelle.Value = Application.WorksheetFunction.Max(rng) Then

          zelle.Interior.ColorIndex = 6

        Else

          zelle.Interior.ColorIndex = xlNone

        End If

      Next

    Next lCol

  End With

End Sub

but this line needs debugging with a runtime error of 1004

  lLastRow = .Cells(.Rows.Count, lCol).End(xlUp).Row

Any advise with regards to the macro above or the line concerned

Much appreciated for your help.

Worksheet is as follows

S/N is in cell C8.

Thanks

Dear Phil,

Thanks for the help.

The macros work for both the min and max values as individual macros controlled by different command buttons. I can also see where the errors occur. Now having solve that part I was wondering if I can combine both the max and min values in one macro but using different colors. Can that be done?

Once again thanks for the help.

Chong