Worksheet change - Target.Column - problem

[wrightyrx7]

Hi all,

I have a problem and I cant get my head round how to fix it.

Basically i have 2 columns with dates in, and i need the dates to be formatted correctly otherwise is messes up some of my formula's. So if the date contains "." or "-" i replace them with "/"

If Target.Column = 1 Or Target.Column = 12 Then        If InStr(1, Target.Value, ".") > 0 Or InStr(1, Target.Value, "-") > 0 Then
            Application.EnableEvents = False
            Target.Value = Replace(Target.Value, ".", "/")
            Target.Value = Replace(Target.Value, "-", "/")
            Application.EnableEvents = True
        End If
End If

So this works when a user is typing into the individual cells.

However, if the user pastes a whole row into the table the code does not run.

How do i get around this?

Thanks

 

[Akuini]

Hi all,

I have a problem and I cant get my head round how to fix it.

Basically i have 2 columns with dates in, and i need the dates to be formatted correctly otherwise is messes up some of my formula's. So if the date contains "." or "-" i replace them with "/"

So this works when a user is typing into the individual cells.

However, if the user pastes a whole row into the table the code does not run.

1. You copy-paste the row as value only, right?
2. Actually if you already format col 1 & 12 as Date using "/" as separator then the "-" will change to "/" automatically.
3. As for the "." you can try this code:
Note: I use loop For Next in case you paste multiple rows at once.

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Columns([COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] Intersect(Target, Columns([COLOR=crimson]1[/COLOR]))
                r.Value = Replace(r.Value, [COLOR=brown]"."[/COLOR], [COLOR=brown]"/"[/COLOR])
        [COLOR=Royalblue]Next[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Columns([COLOR=crimson]12[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] Intersect(Target, Columns([COLOR=crimson]12[/COLOR]))
                r.Value = Replace(r.Value, [COLOR=brown]"."[/COLOR], [COLOR=brown]"/"[/COLOR])
        [COLOR=Royalblue]Next[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

 

[Akuini]

You may also try this, I think this one is better:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)

    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Union(Columns([COLOR=crimson]1[/COLOR]), Columns([COLOR=crimson]12[/COLOR]))) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        Intersect(Target, Union(Columns([COLOR=crimson]1[/COLOR]), Columns([COLOR=crimson]12[/COLOR]))).Replace What:=[COLOR=brown]"."[/COLOR], _
            Replacement:=[COLOR=brown]"/"[/COLOR], LookAt:=xlPart, ReplaceFormat:=[COLOR=Royalblue]False[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]