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

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"DaveM" wrote in message
. uk...
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

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


"DaveM" wrote:

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

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

"DaveM" wrote in message
. uk...
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

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




"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
Why would you not want to use conditional formatting? It is easy and
dynamic.
__________________________________________________ _____________________

"DaveM" wrote in message
. uk...
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

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

"DaveM" wrote:

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




"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
Why would you not want to use conditional formatting? It is easy and
dynamic.
__________________________________________________ _____________________

"DaveM" wrote in message
. uk...
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

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

My code worked for me! Would you like a file with it in?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"DaveM" wrote in message
...
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




"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
Why would you not want to use conditional formatting? It is easy and
dynamic.
__________________________________________________ _____________________

"DaveM" wrote in message
. uk...
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

Thanks All


"Bernard Liengme" wrote in message
...
My code worked for me! Would you like a file with it in?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"DaveM" wrote in message
...
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




"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
Why would you not want to use conditional formatting? It is easy and
dynamic.
__________________________________________________ _____________________

"DaveM" wrote in message
. uk...
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