Updating Cell Value with changing other cell value of same row

nareshjoshy

New Member
Joined
Mar 6, 2019
Messages
23
Hello sir,

I'm using code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)



If Not Intersect(Target, Range("R2:R21")) Is Nothing Then
Cells(Target.Row, "S") = BS2AD(Cells(Target.Row, "R"))
Exit Sub
ElseIf Not Intersect(Target, Range("S2:S21")) Is Nothing Then
Cells(Target.Row, "R") = AD2BS(Cells(Target.Row, "S"))
Exit Sub
End If


If Not Intersect(Target, Range("T2:T21")) Is Nothing Then
Cells(Target.Row, "U") = BS2AD(Cells(Target.Row, "T"))
Exit Sub
ElseIf Not Intersect(Target, Range("U2:U21")) Is Nothing Then
Cells(Target.Row, "T") = AD2BS(Cells(Target.Row, "U"))
Exit Sub
End If


If Not Intersect(Target, Range("V2:V21")) Is Nothing Then
Cells(Target.Row, "W") = BS2AD(Cells(Target.Row, "V"))
Exit Sub
ElseIf Not Intersect(Target, Range("W2:W21")) Is Nothing Then
Cells(Target.Row, "V") = AD2BS(Cells(Target.Row, "W"))
Exit Sub
End If


End Sub

but it shows runtime error 8.

Please help me to sove this problem.

Thanks in advance.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
I always like it when users tell me what their trying to do. Showing me a script you have that does not work with no explanation of what your attempting to do is not something I cannot do.

Please explain in detail what your attempting to do.
 
Upvote 0
What are BS2AD and AD2BS?
 
Upvote 0
Please explain in detail what your attempting to do.
I want to update cell value of same row if I update other cell value.

for i.e. If id update cell(R2) then cell(S2) should be updated only. similerly if i update cell(S2) then cell(R2) should be updated only and vice versa.

But my above code update Cell(S2) as wll as cell(R2) even after updating cell(R2) or cell(S2) and then popup error msg.

What are BS2AD and AD2BS?

BS2AD and AD2BS is my function to change BS date to AD date and AD date to BS date.
 
Upvote 0
Try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Xit
Application.EnableEvents = False
If Not Intersect(Target, Range("R2:R21")) Is Nothing Then
   Cells(Target.Row, "S") = BS2AD(Cells(Target.Row, "R"))
   Exit Sub
ElseIf Not Intersect(Target, Range("S2:S21")) Is Nothing Then
   Cells(Target.Row, "R") = AD2BS(Cells(Target.Row, "S"))
   Exit Sub
ElseIf Not Intersect(Target, Range("T2:T21")) Is Nothing Then
   Cells(Target.Row, "U") = BS2AD(Cells(Target.Row, "T"))
   Exit Sub
ElseIf Not Intersect(Target, Range("U2:U21")) Is Nothing Then
   Cells(Target.Row, "T") = AD2BS(Cells(Target.Row, "U"))
   Exit Sub
ElseIf Not Intersect(Target, Range("V2:V21")) Is Nothing Then
   Cells(Target.Row, "W") = BS2AD(Cells(Target.Row, "V"))
   Exit Sub
ElseIf Not Intersect(Target, Range("W2:W21")) Is Nothing Then
   Cells(Target.Row, "V") = AD2BS(Cells(Target.Row, "W"))
   Exit Sub
End If
Xit:
Application.EnableEvents = True

End Sub
 
Upvote 0
Hello Fluff,

Thank for you code.

Above code works in first update only. after second update there is no effect on cell value change.
 
Upvote 0
Run this
Code:
Sub Chk()
Application.EnableEvents = True
End Sub

and then try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Xit
Application.EnableEvents = False
If Not Intersect(Target, Range("R2:R21")) Is Nothing Then
   Cells(Target.Row, "S") = BS2AD(Cells(Target.Row, "R"))
ElseIf Not Intersect(Target, Range("S2:S21")) Is Nothing Then
   Cells(Target.Row, "R") = AD2BS(Cells(Target.Row, "S"))
ElseIf Not Intersect(Target, Range("T2:T21")) Is Nothing Then
   Cells(Target.Row, "U") = BS2AD(Cells(Target.Row, "T"))
ElseIf Not Intersect(Target, Range("U2:U21")) Is Nothing Then
   Cells(Target.Row, "T") = AD2BS(Cells(Target.Row, "U"))
ElseIf Not Intersect(Target, Range("V2:V21")) Is Nothing Then
   Cells(Target.Row, "W") = BS2AD(Cells(Target.Row, "V"))
ElseIf Not Intersect(Target, Range("W2:W21")) Is Nothing Then
   Cells(Target.Row, "V") = AD2BS(Cells(Target.Row, "W"))
End If
Xit:
If Err.Number <> 0 Then
   MsgBox "Error " & Err.Number & " " & Err.Description & " occured"
End If
Application.EnableEvents = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top