Excel macro to insert uncertain numbers of column with different value

[qiukainus]

Hi

I have a work sheet as below:

Column1 Column2 Column3 Colum4 Column5 Column6
row1 C Y C C_c2 C_c3
END
row2 C N END

For each row in the worksheet, I have to check the Column2,
if the value is "Y",
then I will need to insert 3 columns to the worksheet,
for example, initially row1 is C N END,(three columns only)
After I change the column2 to Y and run the macro, it should change to
C, Y, C, C_c2, C_c3 END.(Six columns)

Similary, for every row, if I change the Column2 value from "Y" to
"N", It should delete next 3 columns after column2.
For example, Initially row1 is C Y C C_c2 C_c3 END,
If i change the Y to N, It should become C N END

Can I get some help to write the excel macro for this?
Thanks in advance!

regards,
Kai

 

[Guest]

Add this code to your worksheet. On the tab on the bottom of the worksheet
click right mouse button and select view code. Copy and paste code shown
below

Private Sub Worksheet_change(ByVal Target As Range)


If Target.Column = 2 Then
If (StrComp(Target, "y") = 0) Or _
(StrComp(Target, "Y") = 0) Then


Columns("C:E").Insert Shift:=xlToRight

Else

If (StrComp(Target, "n") = 0) Or _
(StrComp(Target, "N") = 0) Then

Columns("C:E").Delete Shift:=xlToLeft

End If
End If


End If

End Sub

 

[qiukainus]

Thanks man, but it seems not working...