Highlight Highest number

[DaveM]

Hi all

How could i find down column B with a macro the highest number not
Conditional formatting, so if there's 5 cells with 2007 Highlight all those
cells.

Thanks in advance

Dave

 

[Bernard Liengme]

Sub ColourMyWorld()
Set myRange = Selection
MyMax = WorksheetFunction.Max(myRange)
For Each Mycell In myRange
If Mycell.Value = MyMax Then
Mycell.Interior.ColorIndex = 3
Mycell.Interior.Pattern = xlSolid
End If
Next
End Sub
best wishes

 

[JLGWhiz]

Hi Dave, this macro assumes no header row. You can adjust to suit your
needs, incuding worksheets name or index number and range to be evaluated.
This also allows for more than one cell having the highest single value entry.

Sub mxval()
Set myRng = Worksheets(1).Range("A1:A100")
x = Application.WorksheetFunction.Max(Range("A1:A100"))
For i = 1 To myRng.Rows.Count
If Cells(i, 1).Value = x Then
Cells(i, 1).Interior.ColorIndex = 3
End If
Next
End Sub

 

[Vasant Nanavati]

Why would you not want to use conditional formatting? It is easy and
dynamic.
_______________________________________________________________________

 

[DaveM]

I need to select cells, then save these to a web page, conditional
formatting will not work for what i'm doing

thanks for the replies, the first 2 replies do nothing even when i've change
the code.




Sub mxval()
Set myRng = Worksheets(1).Range("I2:I2542")
x = Application.WorksheetFunction.Max(Range("I2:I2542"))
For i = 1 To myRng.Rows.Count
If Cells(i, 1).Value = x Then
Cells(i, 1).Interior.ColorIndex = 3
End If
Next
End Sub

The highest values in the column are I2535 to I2542 yet the code does not
highlight these cells.

Thanks in advance

Dave

 

[JLGWhiz]

Dave, you will also need to change the column reference from Cells(i, 1) to
Cells(i, 9) to cover Column I. Also make you range reference one cell longer
than your data. For some reason, it doesn't see that last data cell if it is
the last cell in the designated range. i.e. myRng = Range("I2:I2543") I ran
this and it works on my set.

Sub mxval()
Set myRng = Worksheets(1).Range("I2:I2543")
x = Application.WorksheetFunction.Max(Range("I2:I2543"))
For i = 2 To myRng.Rows.Count + 1
If Cells(i, 9).Value = x Then
Worksheets(1).Cells(i, 9).Interior.ColorIndex = 3
End If
Next
End Sub

 

[Rick Rothstein \(MVP - VB\)]

I need to select cells, then save these to a web page, conditional

formatting will not work for what i'm doing

Maybe this function will help you. Pass it the column (as a letter) you want
to parse and it will return a range containing the cells with the highest
value in that column. You can then process the returned range however you
want to.

Function HighestSelection(ColumnLetter As String) As Range
Dim R As Range
Dim MaxValue As Double
Dim CumulativeSelection As String
MaxValue = -1E+308
For Each R In Range(ColumnLetter & ":" & ColumnLetter)
If R.Value > MaxValue Then
CumulativeSelection = R.Address
MaxValue = R.Value
ElseIf R.Value = MaxValue Then
CumulativeSelection = CumulativeSelection & "," & R.Address
End If
Next
Set HighestSelection = Range(CumulativeSelection)
End Function


Rick

 

[Bernard Liengme]

My code worked for me! Would you like a file with it in?

 

[DaveM]

Thanks All

My code worked for me! Would you like a file with it in?