Dear Expert,
I need to add a new column in the spreadsheet as days goes by. Add column in
one day and old data keeps moving to right...
That's, calendar date in column A and new data in Column B and old data
(from Column C) keeps moving from left to right for each working days on and
on.
Excel column can go up to max 255 (around, exact max columns I can't recall).
End column is IV ?? (Anyway, it doesn't matter).
If data hits column IR, I need to do a back-up to save the old data from
column A to column IR.
Can I set a Function (Alert) in Column IS (few columns ahead of IV and one
column later than IR) if data keeps moving to left till hitting the column IR
so that it can run a macro to pop up a msgbox to alert me to back up old data
please ?
If it is blank in IR, function (alert) in IS and thus macro would not be
triggered.
Thanks so much.

Put this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim w As WorksheetFunction
Dim I As Range
Set I = Range("IS:IS")
Set w = Application.WorksheetFunction
x = w.CountA(I)
If x 0 Then
MsgBox ("warning - data in column IS")
End If
End Sub

The warning will appear as soon as data has been pushed into column IS.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200849


"Elton Law" wrote:

Dear Expert,
I need to add a new column in the spreadsheet as days goes by. Add column in
one day and old data keeps moving to right...
That's, calendar date in column A and new data in Column B and old data
(from Column C) keeps moving from left to right for each working days on and
on.
Excel column can go up to max 255 (around, exact max columns I can't recall).
End column is IV ?? (Anyway, it doesn't matter).
If data hits column IR, I need to do a back-up to save the old data from
column A to column IR.
Can I set a Function (Alert) in Column IS (few columns ahead of IV and one
column later than IR) if data keeps moving to left till hitting the column IR
so that it can run a macro to pop up a msgbox to alert me to back up old data
please ?
If it is blank in IR, function (alert) in IS and thus macro would not be
triggered.
Thanks so much.

Hi Gary,
That really works ... this is not the first time for you to help. I can
recall it. Very much appreciated !!!!
Elton

"Gary''s Student" wrote:

Put this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim w As WorksheetFunction
Dim I As Range
Set I = Range("IS:IS")
Set w = Application.WorksheetFunction
x = w.CountA(I)
If x 0 Then
MsgBox ("warning - data in column IS")
End If
End Sub

The warning will appear as soon as data has been pushed into column IS.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200849


"Elton Law" wrote:

Dear Expert,
I need to add a new column in the spreadsheet as days goes by. Add column in
one day and old data keeps moving to right...
That's, calendar date in column A and new data in Column B and old data
(from Column C) keeps moving from left to right for each working days on and
on.
Excel column can go up to max 255 (around, exact max columns I can't recall).
End column is IV ?? (Anyway, it doesn't matter).
If data hits column IR, I need to do a back-up to save the old data from
column A to column IR.
Can I set a Function (Alert) in Column IS (few columns ahead of IV and one
column later than IR) if data keeps moving to left till hitting the column IR
so that it can run a macro to pop up a msgbox to alert me to back up old data
please ?
If it is blank in IR, function (alert) in IS and thus macro would not be
triggered.
Thanks so much.