Worksheet Change Event VBA Code

[Nitesh Khot]

Hi..

I have write vba worksheet event change code where i select value from dropdown then automatically another cell value need to be changed....but i am getting "0" value in single cell..

anyone can suggest how to do this..

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, ActiveCell) Is Nothing Then
    Select Case ActiveCell
       
        Case "9616":
       
        If ActiveCell.Offset(0, -3).Value = "Husband" And ActiveCell.Offset(0, -2).Value <= "58" And ActiveCell.Offset(0, -1).Value = "DOUBLE" Then
       
             ActiveCell.Offset(0, 1).Value = "9368.86" And ActiveCell.Offset(0, 2).Value = "10107" And ActiveCell.Offset(0, 3).Value = "SV-10K-12M"
                Else
               
            ActiveCell.Offset(0, 1).Value = "9437.38" And ActiveCell.Offset(0, 2).Value = "10107" And ActiveCell.Offset(0, 3).Value = "SV-10K-12M"
       
        End If
       
        Case "11753":
       
            If ActiveCell.Offset(0, -3).Value = "HUSBAND" And ActiveCell.Offset(0, -2).Value >= 58 And ActiveCell.Offset(0, -1).Value = "DOUBLE" Then
       
             ActiveCell.Offset(0, 1).Value = "11481.39" And ActiveCell.Offset(0, 2).Value = "10108" And ActiveCell.Offset(0, 3).Value = "SV-12K-12M"
                Else
            ActiveCell.Offset(0, 1).Value = "11549.91" And ActiveCell.Offset(0, 2).Value = "10108" And ActiveCell.Offset(0, 3).Value = "SV-12K-12M"
       
        End If
       
       
    End Select
End If
End Sub

thanks
nikh

 

[Hui]

Your code is in the Code Module for Sheet 2, not sheet 1
Select it all, Cut
Select code module 1
Paste

 

[Hui]

I would change your code as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, ActiveCell) Is Nothing Then
  Select Case ActiveCell.Value
   
  Case 9616
   
  If ActiveCell.Offset(0, -3).Value = "Husband" And ActiveCell.Offset(0, -2).Value <= "58" And ActiveCell.Offset(0, -1).Value = "DOUBLE" Then
   
  ActiveCell.Offset(0, 1).Value = "9368.86"
  ActiveCell.Offset(0, 2).Value = "10107"
  ActiveCell.Offset(0, 3).Value = "SV-10K-12M"
  Else
   
  ActiveCell.Offset(0, 1).Value = "9437.38"
  ActiveCell.Offset(0, 2).Value = "10107"
  ActiveCell.Offset(0, 3).Value = "SV-10K-12M"
   
  End If
   
  Case 11753
   
  If ActiveCell.Offset(0, -3).Value = "HUSBAND" And ActiveCell.Offset(0, -2).Value >= 58 And ActiveCell.Offset(0, -1).Value = "DOUBLE" Then
   
  ActiveCell.Offset(0, 1).Value = "11481.39"
  ActiveCell.Offset(0, 2).Value = "10108"
  ActiveCell.Offset(0, 3).Value = "SV-12K-12M"
  Else
  ActiveCell.Offset(0, 1).Value = "11549.91"
  ActiveCell.Offset(0, 2).Value = "10108"
  ActiveCell.Offset(0, 3).Value = "SV-12K-12M"
   
  End If
   
   
  End Select
End If
Application.EnableEvents = True

End Sub

 

[Nitesh Khot]

Thanks for your most valuable answer......exactly wht i m want..thanks