conditional if statement and macro

[Art Cummings]

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

 

[Jacob Skaria]

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

 

[Don Guillett]

right click sheet tab>view code>insert 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

 

[Art Cummings]

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

Art

 

[Art Cummings]

Thank i'm leaving work but will try tomorrow.



Art

 

[LOFE]

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]

Thank you,

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

Art