how to select the max value in a range

[nicitalia]

Hi guys,

I've got a column in which are entered different values. I want to create a sub that choose the maximun of these values and that will fill the cell with red color.
I want to do this through the Worksheet_change (ByVal Target As Range) event, so every time that a value is entered in the column the sub automatically will process the range and will find out the maximun value.

I attached a link with an example xls.

Hope someone could help me...

thank you in advance.

Nic

 

[mintjulep]

You can achieve this with Conditional Formatting. No VBA necessary.

 

[nicitalia]

I don't think in this case the Conditional Formatting is enough. The problem is that I've a dynamic range in wich the user do the data entry. Every time that a data has been entered the program must look at the range and choose the higher value.

 

[nicitalia]

How is possibile to use the MAX() function within the Conditional Formatting?

 

[nicitalia]

Ok, I've made it, here's the code (to be written in the sheet module).
--------------------------------
Private Sub Worksheet_Calculate()
Application.CalculateFull
End Sub

'this sub allow excel to read the changes releated to the calculation on functions on the sheet. If changes happen then starts the "change" event below:
---------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("Nuove_visite"), Target) Is Nothing _
Then Exit Sub

'the secret is that "Nuove_visite" it's a dynamic range: it 'means that everytime that an user inserts a number in a cell of 'this range, the for...next cycle check the whole range!!!

X = Application.WorksheetFunction.Max(Range("Nuove_visite"))
'the sub will check the range and when the highest value is 'found, the font of the cell will be colored red.

MsgBox X 'just to verify if it's the highest number
For Each C In Range("Nuove_visite")
If C.Value >= X Then
C.Font.ColorIndex = 3
Else
C.Font.ColorIndex = 1
End If
Next

End Sub
--------------------------------------
Hope this is clear, if someone wants an example please write here.

Bye bye
Nic

 

[SkipVought]

Your code is not necessary! This can easly be accomplished in the COnditional Format feature. The range can be dynamic, depending how you define the range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Skips instructions are best. If you really want another way, one other thing that you can do - and I don't recommend it for this particular option - is as follows

Dim ilastrow as integer
Dim i as integer

' Count total number of rows using column A
ilastrow = Sheets("Sheet1").Range("A1").End(xlDown).Row

' Add one to the number
i = ilastrow + 1

' Put your formula in the row below the data
Range("A" & i).value = max("A1:A" & i)

Note that if there is an empty cell the row count will stop at the empty cell.

 

[UnsolvedCoding]

Sorry - that last line of code should end with

= max("A1:A" & ilastrow)