VBA code to skip entire row from Column AD to BO

[sshrikanth2]

Dear All,

I have a coding which skips entire row from Column AD to BE, when I select non-core activities from drop-down list (Eg : Training, Meeting, etc.).</SPAN>

I want this to extend till Column BO. Kindly help me correcting this code.

Following is the current code i am using :

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("MDC").Unprotect Password:="1234"
cellrow = ActiveCell.Row
cellcolumn = ActiveCell.Column
'Range(ActiveCell, 27).Select
If ActiveCell.Value <> "QC" Then
Cells(cellrow, 20).Select
Range(ActiveCell, ActiveCell.Offset(i, 37)).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, 37)).Select
Selection.ClearContents
Cells(cellrow, cellcolumn).Select
ActiveWorkbook.Worksheets("MDC").Protect Password:="1234"
Else
Cells(cellrow, 5).Value = Cells(cellrow, 67).Value
ActiveWorkbook.Worksheets("MDC").Protect Password:="1234"
End If
End If
End Sub



Regards,

Srikanth M

</SPAN>

 

[craig.penny]

When you have

Cells(cellrow,20)

cellrow is the row and 20 is the column. Column 20 is Column T. When you follow the previous line with

.Offset(i, 37))

The column is now 57 (20+37), which is BE. I think you need to change that to

.Offset(i, 47))

which will put you at column 67 (20+47), BO.