I have achieved the desired results with following MACRO for the simplest case i.e. it is calculating Basic, HRA, Bonus, EPF, ESIC. I need to refine this code (make it more simple and efficient) and apply the same logic for calculating Conv., Med., CCA and SPA Values. There is one more problem, this code only works when i make changes in cell O8 (increment %) and press tab. if i use left, upper or down arrow the code doesnot work. the code should work as soon as there is a change in cell O8. Can somebody help me in this regard?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$8" Then



Dim NewCTC As Double
Dim GrossIncrease As Integer
Dim NewBasic As Double
Dim NewBonus As Double
Dim EPF As Double
Dim FinalCTC As Double

Dim TakeHome As Double
FinalCTC = 0

NewCTC = ActiveCell.Previous.Offset(0, -1).Value + (ActiveCell.Previous.Offset(0, -1).Value) * ActiveCell.Previous.Value
GrossIncrease = NewCTC - ActiveCell.Previous.Offset(0, -1).Value
ActiveCell.Previous.Offset(0, 1).Value = NewCTC
ActiveCell.Previous.Offset(0, 2).Value = GrossIncrease

'New Basic Start
Select Case ActiveCell.Previous.Offset(0, -13).Value
Case 1
NewBasic = WorksheetFunction.Max(NewCTC * 0.26, 9200)
Case 2
NewBasic = WorksheetFunction.Max(NewCTC * 0.26, 8200)
Case 3
NewBasic = WorksheetFunction.Max(NewCTC * 0.26, 7800)
End Select
ActiveCell.Previous.Offset(0, 3).Value = NewBasic
'New Basic End


'New Bonus Start
NewBonus = NewBasic * 0.2
ActiveCell.Previous.Offset(0, 10).Value = NewBonus
'New Bonus End

'New EPF Start
NewEPF = NewBasic * 0.12
ActiveCell.Previous.Offset(0, 11).Value = NewEPF
'New EPF End


'FOR Freezing HRA Value - New HRA+ESIC Start
NewHRA = NewBasic * 0.5
NewTakeHome = NewBasic + NewHRA
If NewTakeHome < 21000 Then
NewESIC = NewTakeHome * 0.0425
Else
NewESIC = 0
End If
FinalCTC = NewBasic + NewHRA + NewBonus + NewEPF + NewESIC
If FinalCTC > NewCTC Then
Do While (Round(FinalCTC, 0) <> Round(NewCTC, 0))
NewHRA = NewHRA - 1
NewTakeHome = NewBasic + NewHRA
If NewTakeHome < 21000 Then
NewESIC = NewTakeHome * 0.0425
Else
NewESIC = 0
End If
FinalCTC = NewBasic + NewHRA + NewBonus + NewEPF + NewESIC
If NewHRA = 0 Then
Exit Do
End If
Loop
End If
ActiveCell.Previous.Offset(0, 4).Value = Round(NewHRA, 0)
ActiveCell.Previous.Offset(0, 12).Value = Round(NewESIC, 0)
ActiveCell.Previous.Offset(0, 13).Value = Round(FinalCTC, 0)
'New HRA+ESIC End








End If
End Sub