using now() as static function

Hi
Experts

I am preparing excel worksheet. I have developed macro in that sheet. The
purpose of that macro is " When programmer finishes programming, runs macro
using shortut key. The result is that particular cell changes colour to
green and 1 gets typed in it. No problem with that. In addition to that I
have used NOW() function to get the particular date and time in the cell just
adjacent to it on right hand side. When Programmer finishes programming for
different jobs and try to update the worksheet for particularjob by running
macro, THE WHOLE WORKSHEET GETS UPDATED to current date and time, which is
actually a problem.

My question is How can I make NOW() to work so it just puts specific date
and time and doesn't update entire worksheet?

Instead I try to use Ctrl+( and Ctrl +Shift+( to get the specific date
and time.

The problem here is that "It is acting static and whenever I try to Update
the programming cell for a particular job it gives the previous date and
time".

I will be thankful if somebody can give me solution to both these problems!!!

HAPPY PROBLEM SOLVING

Minal

Take a look here:

http://www.mcgimpsey.com/excel/timestamp.html

In article <[email protected]>,
"MINAL ZUNKE" <MINAL [email protected]> wrote:

> Hi
> Experts
>
> I am preparing excel worksheet. I have developed macro in that sheet. The
> purpose of that macro is " When programmer finishes programming, runs macro
> using shortut key. The result is that particular cell changes colour to
> green and 1 gets typed in it. No problem with that. In addition to that I
> have used NOW() function to get the particular date and time in the cell just
> adjacent to it on right hand side. When Programmer finishes programming for
> different jobs and try to update the worksheet for particularjob by running
> macro, THE WHOLE WORKSHEET GETS UPDATED to current date and time, which is
> actually a problem.
>
> My question is How can I make NOW() to work so it just puts specific date
> and time and doesn't update entire worksheet?
>
> Instead I try to use Ctrl+( and Ctrl +Shift+( to get the specific date
> and time.
>
> The problem here is that "It is acting static and whenever I try to Update
> the programming cell for a particular job it gives the previous date and
> time".
>
> I will be thankful if somebody can give me solution to both these problems!!!
>
> HAPPY PROBLEM SOLVING
>
> Minal

hi Je Mc Gimpsey

The solution you gave me is working. thanks a lot.

one small problem, What I am trying to do is if I press digit 1 then only it
should automatically enter date in the adjacent column.

with your solution it works if I type any digit which is not my requirement.
In my requirement it should work only when I type "1" and for anyother
digit is should show "NA".
if you can give me a solution for that I will be very thankful.

thanks




"JE McGimpsey" wrote:

> Take a look here:
>
> http://www.mcgimpsey.com/excel/timestamp.html
>
> In article <[email protected]>,
> "MINAL ZUNKE" <MINAL [email protected]> wrote:
>
> > Hi
> > Experts
> >
> > I am preparing excel worksheet. I have developed macro in that sheet. The
> > purpose of that macro is " When programmer finishes programming, runs macro
> > using shortut key. The result is that particular cell changes colour to
> > green and 1 gets typed in it. No problem with that. In addition to that I
> > have used NOW() function to get the particular date and time in the cell just
> > adjacent to it on right hand side. When Programmer finishes programming for
> > different jobs and try to update the worksheet for particularjob by running
> > macro, THE WHOLE WORKSHEET GETS UPDATED to current date and time, which is
> > actually a problem.
> >
> > My question is How can I make NOW() to work so it just puts specific date
> > and time and doesn't update entire worksheet?
> >
> > Instead I try to use Ctrl+( and Ctrl +Shift+( to get the specific date
> > and time.
> >
> > The problem here is that "It is acting static and whenever I try to Update
> > the programming cell for a particular job it gives the previous date and
> > time".
> >
> > I will be thankful if somebody can give me solution to both these problems!!!
> >
> > HAPPY PROBLEM SOLVING
> >
> > Minal
>

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim bValid As Boolean
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
If IsNumeric(.Value) Then _
If .Value = 1 Then _
bValid = True
With .Offset(0, 1)
If bValid Then
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
Else
.NumberFormat = "General"
.Value = "NA"
End If
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

In article <[email protected]>,
"MINAL ZUNKE" <[email protected]> wrote:

> The solution you gave me is working. thanks a lot.
>
> one small problem, What I am trying to do is if I press digit 1 then only it
> should automatically enter date in the adjacent column.
>
> with your solution it works if I type any digit which is not my requirement.
> In my requirement it should work only when I type "1" and for anyother
> digit is should show "NA".
> if you can give me a solution for that I will be very thankful.