Hi All:
I need help writing a macro. Here's my problem, I would like to get the maximum value of each range a column. For instance, I have two columns of data, column A contains sensor numbers, while column B contains sensor temperature readings, like so
column A............. column B
sensor #............sensor temp. (F)
N2 ...................... 100
N2 ...................... 115
N2..........................90
S3........................120
S3........................115
W4........................119
E1........................100
E1........................111
E1........................112
......and so.
I want a macro that takes into consideration that sensor numbers N2, S3, W4, and E1 entered in column A are all separate and distinct ranges. Once the ranges are is established I would like the macro to determine the maximum value of each range, report that value in a message box and change the background color of the cell.
Here is the code that I have come up with:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Sub Get_Max_Value_of_Ranges()
Dim rnga, rngb, Max, Rng, As Range
Dim i as Integer
Set rnga = ActiveSheet.Range("A7:A100")
Set rngb = ActiveSheet.Range("B7:B100")
For i = 7 to 100
Set Rng = Intersection(rnga, rngf)
Set MaxRng = Rng.Cells(Application.Match(Application.Max(Rng), Rng, 0)
Msgbox "Max. Value for" & rnga & "=" & MaxRng
MaxRng.Interior.ColorIndex = 3
Set rngf = rngf.Resize(rngf.rows.Count + 1, rngf.Columns.Count)
Next i
End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Thanks in advance for any help.
Chris