Greetings all,

I'd like to create a macro that uses the <ctrl; shortcut to automatically
enter the current date only if the adjacent cell to the left has a certain
value.

for example:
if A1="Y" or A1="N" then
"run macro in current cell B1. "

This macro needs to run whenever a user is in any cell in column B and it
should check the adjacent cell.

I want to insert a static date because i don't want the date to change once
inserted.

Example:

A1 B1
Y run macro(current date)
A2 B2
do nothing
A3 B3
N run macro(current date)

I'm not sure how to have the macro look at an adjacent cell, nor am I sure
how to have the macro run for a specific column of active cells.

I hope this request for help is clear, if it's not, I can suppy additional
information.

Thanks

Art

Try using the below event..and use Target.Column and .Row to identify that
the user is in ColB. and based on the condition call your macro..

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Column = 2 Then
strColAValue = Range("A" & Target.Row)
If strColAValue = "Y" then Call <YourMacro
End If
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"Art Cummings" wrote:

Greetings all,

I'd like to create a macro that uses the <ctrl; shortcut to automatically
enter the current date only if the adjacent cell to the left has a certain
value.

for example:
if A1="Y" or A1="N" then
"run macro in current cell B1. "

This macro needs to run whenever a user is in any cell in column B and it
should check the adjacent cell.

I want to insert a static date because i don't want the date to change once
inserted.

Example:

A1 B1
Y run macro(current date)
A2 B2
do nothing
A3 B3
N run macro(current date)

I'm not sure how to have the macro look at an adjacent cell, nor am I sure
how to have the macro run for a specific column of active cells.

I hope this request for help is clear, if it's not, I can suppy additional
information.

Thanks

Art

right click sheet tabview codeinsert this. Now when you enter a or y in
col a the date will appear in col b. It won't change unless you change THAT
cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column < 1 Then Exit Sub
If UCase(Target) = "A" Or _
UCase(Target) = "Y" Then
Target.Offset(, 1) = Date
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Art Cummings" wrote in message
...
Greetings all,

I'd like to create a macro that uses the <ctrl; shortcut to automatically
enter the current date only if the adjacent cell to the left has a certain
value.

for example:
if A1="Y" or A1="N" then
"run macro in current cell B1. "

This macro needs to run whenever a user is in any cell in column B and it
should check the adjacent cell.

I want to insert a static date because i don't want the date to change
once inserted.

Example:

A1 B1
Y run macro(current date)
A2 B2
do nothing
A3 B3
N run macro(current date)

I'm not sure how to have the macro look at an adjacent cell, nor am I sure
how to have the macro run for a specific column of active cells.

I hope this request for help is clear, if it's not, I can suppy additional
information.

Thanks

Art

Thanks, i'm leaving work, but will try tomorrow.

Art
"Jacob Skaria" wrote in message
...
Try using the below event..and use Target.Column and .Row to identify that
the user is in ColB. and based on the condition call your macro..

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Column = 2 Then
strColAValue = Range("A" & Target.Row)
If strColAValue = "Y" then Call <YourMacro
End If
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"Art Cummings" wrote:

Greetings all,

I'd like to create a macro that uses the <ctrl; shortcut to
automatically
enter the current date only if the adjacent cell to the left has a
certain
value.

for example:
if A1="Y" or A1="N" then
"run macro in current cell B1. "

This macro needs to run whenever a user is in any cell in column B and
it
should check the adjacent cell.

I want to insert a static date because i don't want the date to change
once
inserted.

Example:

A1 B1
Y run macro(current date)
A2 B2
do nothing
A3 B3
N run macro(current date)

I'm not sure how to have the macro look at an adjacent cell, nor am I
sure
how to have the macro run for a specific column of active cells.

I hope this request for help is clear, if it's not, I can suppy
additional
information.

Thanks

Art

Thank i'm leaving work but will try tomorrow.



Art
"Don Guillett" wrote in message
...
right click sheet tabview codeinsert this. Now when you enter a or y in
col a the date will appear in col b. It won't change unless you change
THAT cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column < 1 Then Exit Sub
If UCase(Target) = "A" Or _
UCase(Target) = "Y" Then
Target.Offset(, 1) = Date
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Art Cummings" wrote in message
...
Greetings all,

I'd like to create a macro that uses the <ctrl; shortcut to
automatically enter the current date only if the adjacent cell to the
left has a certain value.

for example:
if A1="Y" or A1="N" then
"run macro in current cell B1. "

This macro needs to run whenever a user is in any cell in column B and
it should check the adjacent cell.

I want to insert a static date because i don't want the date to change
once inserted.

Example:

A1 B1
Y run macro(current date)
A2 B2
do nothing
A3 B3
N run macro(current date)

I'm not sure how to have the macro look at an adjacent cell, nor am I
sure how to have the macro run for a specific column of active cells.

I hope this request for help is clear, if it's not, I can suppy
additional information.

Thanks

Art

Can't you just have it so that on pressing the shortcut to execute the macro,
the macro would be something along the lines of

If Activecell = "Y" or Activecell = "N" Then
Activecell(1,2) = Now() 'Or Format(Now(),"dd-mmm-yyyy") etc
End If

That's assuming a user selects a cell and then presses <CTRL ;.

If you want to do all of the rows, just do a loop

Range("A1").Select
Do Until Activecell = ""
If Activecell = "Y" or Activecell = "N" Then
Activecell(1,2) = Now() 'Or Format(Now(),"dd-mmm-yyyy") etc
End If
Activecell(2,1).Select 'To go to the next row
Loop

Not the most elegant I guess but easy to understand and maintain.

"Art Cummings" wrote:

Thank i'm leaving work but will try tomorrow.



Art
"Don Guillett" wrote in message
...
right click sheet tabview codeinsert this. Now when you enter a or y in
col a the date will appear in col b. It won't change unless you change
THAT cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column < 1 Then Exit Sub
If UCase(Target) = "A" Or _
UCase(Target) = "Y" Then
Target.Offset(, 1) = Date
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Art Cummings" wrote in message
...
Greetings all,

I'd like to create a macro that uses the <ctrl; shortcut to
automatically enter the current date only if the adjacent cell to the
left has a certain value.

for example:
if A1="Y" or A1="N" then
"run macro in current cell B1. "

This macro needs to run whenever a user is in any cell in column B and
it should check the adjacent cell.

I want to insert a static date because i don't want the date to change
once inserted.

Example:

A1 B1
Y run macro(current date)
A2 B2
do nothing
A3 B3
N run macro(current date)

I'm not sure how to have the macro look at an adjacent cell, nor am I
sure how to have the macro run for a specific column of active cells.

I hope this request for help is clear, if it's not, I can suppy
additional information.

Thanks

Art

Thank you,

I was able to get it to work with your code.

Art
"Art Cummings" wrote in message
...
Thanks, i'm leaving work, but will try tomorrow.

Art
"Jacob Skaria" wrote in message
...
Try using the below event..and use Target.Column and .Row to identify
that
the user is in ColB. and based on the condition call your macro..

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
As Range)
If Target.Column = 2 Then
strColAValue = Range("A" & Target.Row)
If strColAValue = "Y" then Call <YourMacro
End If
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"Art Cummings" wrote:

Greetings all,

I'd like to create a macro that uses the <ctrl; shortcut to
automatically
enter the current date only if the adjacent cell to the left has a
certain
value.

for example:
if A1="Y" or A1="N" then
"run macro in current cell B1. "

This macro needs to run whenever a user is in any cell in column B and
it
should check the adjacent cell.

I want to insert a static date because i don't want the date to change
once
inserted.

Example:

A1 B1
Y run macro(current date)
A2 B2
do nothing
A3 B3
N run macro(current date)

I'm not sure how to have the macro look at an adjacent cell, nor am I
sure
how to have the macro run for a specific column of active cells.

I hope this request for help is clear, if it's not, I can suppy
additional
information.

Thanks

Art