Recording highest value dynamic cells
 

If I have a cell A1 in which the value dynamically changes and in cell A2 i
want it to record that value what should i do? An earlier response i got was
to use this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Range("a1") Range("a2") Then Range("a2") = Range("a1")
End Sub

However i need to do it for multiple cells. Not knowing VBA i tried adding
more "IF" statements but that didn't work. I treid copying and pasting and
changing the values (IE from A1 to B1) but i got an ambigious error for
"Worksheet_change"
Any help would be appreciated thanks.

Recording highest value dynamic cells
 

Hi,

Can you explain a bit more in terms of "Want to do it for multiple cells"?

Cheers,

Grant.

"Gupta A." wrote:

If I have a cell A1 in which the value dynamically changes and in cell A2 i
want it to record that value what should i do? An earlier response i got was
to use this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Range("a1") Range("a2") Then Range("a2") = Range("a1")
End Sub

However i need to do it for multiple cells. Not knowing VBA i tried adding
more "IF" statements but that didn't work. I treid copying and pasting and
changing the values (IE from A1 to B1) but i got an ambigious error for
"Worksheet_change"
Any help would be appreciated thanks.

Recording highest value dynamic cells
 

Maybe this is what you want

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target.Rang("A1:A10")) Is Nothing Then
With Target
If .Value .Offset(0,1).Value Then
.Offset(0,1).Value = .value
End If
End With
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gupta A." wrote in message
...
If I have a cell A1 in which the value dynamically changes and in cell A2
i
want it to record that value what should i do? An earlier response i got
was
to use this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Range("a1") Range("a2") Then Range("a2") = Range("a1")
End Sub

However i need to do it for multiple cells. Not knowing VBA i tried adding
more "IF" statements but that didn't work. I treid copying and pasting and
changing the values (IE from A1 to B1) but i got an ambigious error for
"Worksheet_change"
Any help would be appreciated thanks.

Recording highest value dynamic cells
 

Erm, thats not exactly what I meant, sorry. The example I gave above

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Range("a1") Range("a2") Then Range("a2") = Range("a1")
End Sub

- (When written in the sheets code) works for a singular cell only. For
example if I wanted to create a spreadsheet about employees and i want to see
the maximum number of toys th worker produced. I have five workers so I need
to that maximum saved for five different people. Now the example above works
for ONE employee. If i add additional if statements it ignores the additional
one (so i turn the code into)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Range("a1") Range("a2") Then Range("a2") = Range("a1")
If Range("b1") Range("b2") Then Range("b2") = Range("b1")
End Sub

But all other "IF" statements are ignored. I also tried using two different
pieces of code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Range("a1") Range("a2") Then Range("a2") = Range("a1")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$1" Then Exit Sub
If Range("b1") Range("b2") Then Range("b2") = Range("b1")
End Sub

But that didn't work either - I got an error saying that the SECOND
"Worksheet_change" statement was ambigious - although th first worked fine.

I appreciate the help - and would appreciate any extra - Thanks.

Recording highest value dynamic cells
 

I think what I originally gave you was long the right lines, just needs to
be tweaked

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target.Rang("A1:E1")) Is Nothing Then
With Target
If .Value .Offset(1,0).Value Then
.Offset(1,0).Value = .value
End If
End With
End If
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gupta A." wrote in message
...
Erm, thats not exactly what I meant, sorry. The example I gave above

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Range("a1") Range("a2") Then Range("a2") = Range("a1")
End Sub

- (When written in the sheets code) works for a singular cell only. For
example if I wanted to create a spreadsheet about employees and i want to
see
the maximum number of toys th worker produced. I have five workers so I
need
to that maximum saved for five different people. Now the example above
works
for ONE employee. If i add additional if statements it ignores the
additional
one (so i turn the code into)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Range("a1") Range("a2") Then Range("a2") = Range("a1")
If Range("b1") Range("b2") Then Range("b2") = Range("b1")
End Sub

But all other "IF" statements are ignored. I also tried using two
different
pieces of code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
If Range("a1") Range("a2") Then Range("a2") = Range("a1")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$1" Then Exit Sub
If Range("b1") Range("b2") Then Range("b2") = Range("b1")
End Sub

But that didn't work either - I got an error saying that the SECOND
"Worksheet_change" statement was ambigious - although th first worked
fine.

I appreciate the help - and would appreciate any extra - Thanks.