Can someone kindly point me to some macros for highlighting max and min values in an excel sheet with data in columns D8 to f20. Examples from the net returns a lot of errors which I do not comprehend as Iam a newbie. Thanks and much appreciated.
Chong
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.
Hi,
Can you supply the workbook please.
Phil
Worksheet is as follows
| S/N | Name | Gender | English | Maths | Physics | |||
| 1 | Jane | female | 98 | 89 | 77 | |||
| 2 | John | male | 85 | 86 | 76 | |||
| 3 | Jennifer | female | 72 | 78 | 82 | |||
| 4 | Matthew | male | 68 | 79 | 85 | |||
| 5 | Marcus | male | 75 | 81 | 78 | |||
| 6 | Mary | female | 69 | 74 | 59 | |||
| 7 | Simon | male | 82 | 65 | 74 | |||
| 8 | Timothy | male | 59 | 88 | 69 | |||
| 9 | Dennis | male | 78 | 76 | 74 | |||
| 10 | Alex | male | 62 | 71 | 62 | |||
| 11 | Aaron | male | 87 | 68 | 72 | |||
| 12 | Edith | female | 77 | 59 | 70 | |||
S/N is in cell C8.
Thanks
Hi Chong,
See attached workbook.
Regards
Phil
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
No worries.
After the set rng statement modify your code to this
rng.Interior.ColorIndex = xlNone
For Each zelle In rng
If zelle.Value = Application.WorksheetFunction.Max(rng) Then
zelle.Interior.ColorIndex = 6
End If
If zelle.Value = Application.WorksheetFunction.Min(rng) Then
zelle.Interior.ColorIndex = 7
End If
Next
Regards
Phil
