VB code to skip rows and keep formula immovable

[sshrikanth2]

Hi,

I need your help...

I have a VB code which skips the row from Column 20 to Column 67 but i have a formula in "Column Z" and "Column AA" respectively. When i execute the skip row function the formula will move one step down vertically and the upper cell or row remains blank but i want the formula remain as it is even though i perform skip function. I appreciate your help.

Formula - Column Z4:Z5000 =IF(SUM(L4:Y4)>0,SUM(L4:Y4),0)
Column AA4:AA5000 =IF(Z4=0,0,"1")



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Task As String
Dim Cell_To_Test As Range, Cells_Changed As Range
Dim Email, TechName, Remarks As Integer

Set Cells_Changed = Target(1, 1)
Set Cell_To_Test = Range("C4:C100000")
If Not Intersect(Cells_Changed, Cell_To_Test) Is Nothing Then
ActiveWorkbook.Worksheets("Quality Check Log").Unprotect Password:="RAJU"
cellrow = ActiveCell.Row
cellcolumn = ActiveCell.Column
'Range(ActiveCell, 27).Select
If ActiveCell.Value <> "QualityCheck" Then
Cells(cellrow, 20).Select
Range(ActiveCell, ActiveCell.Offset(i, 47)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Cells(CellRow, 43).Select
'Range(ActiveCell, ActiveCell.Offset(i, 0)).Select
'ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
'Selection.Copy
'ActiveCell.Offset(1, 0).Select
'Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Range(ActiveCell, ActiveCell.Offset(i, 0)).Select
ActiveCell.Offset(-1, 0).Select
Range(ActiveCell, ActiveCell.Offset(i, 47)).Select
Selection.ClearContents
Cells(cellrow, cellcolumn).Select
ActiveWorkbook.Worksheets("Quality Check Log").Protect Password:="RAJU"
Else
Cells(cellrow, 5).Value = Cells(cellrow, 67).Value
ActiveWorkbook.Worksheets("Quality Check Log").Protect Password:="RAJU"
End If
End If
End Sub


Regards,

Srikanth M