worksheet change and offset 1

[robcarr]

Dear All,

I am using the coding below to put value entered in aux data into all sheets that I require, the problem is I have to use the offset button to revert to the cell that was changed, this only works if someone enters data and then presses return, if they just use a right arrow it will not work on the correct cell, is there a way to know which cell was changed and then put this in all the other sheets, I have given part of the case select coding here.

If Not Intersect(Target, Range("b5:Af51&quot) Is Nothing Then
UserSel2 = ActiveWindow.RangeSelection.Offset(-1, 0).Address
userval2 = ActiveWindow.RangeSelection.Offset(-1, 0).Value
Dim UserSheet As Worksheet, sht As Worksheet
Dim UserSel As String
Set UserSheet = ActiveSheet
Application.ScreenUpdating = False
Select Case UCase(Target.Text)

Case "DO"
If TypeName(ActiveSheet) <> &quot;Worksheet&quot; Then Exit Sub
' Remember the current sheet
' Store info from the active sheet
UserSel = UserSel2
userval = userval2
' Loop through the worksheets
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> &quot;Agent Info&quot; Then
If sht.Name <> &quot;Welcome&quot; Then
If sht.Name <> &quot;Aux Data&quot; Then
If sht.Name <> &quot;Monitoring&quot; Then
If sht.Visible Then 'skip hidden sheets
sht.Activate
sht.Range(UserSel).Select
ActiveCell.Value = userval
sht.Range(UserSel).Interior.ColorIndex = 37
End If
End If
End If
End If
End If
Next sht
' Restore the original position
UserSheet.Activate

Any help on this would be greatly appreciated.



Thanks Rob.

 

[xlbo]

Target is the keyword here. In the change event
Target refers to the cell that was changed. It is a range object and therefore has all associated properties..
target.Address
target.row
target.column
target.value
etc etc etc

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[robcarr]

another star I believe is deserved, thanks Geoff, worked like a charm,

Rob.

Thanks Rob.