If anything in cell.. then put period in cell beside

[Xx7]

If there is anything in a cell in Column D, then I want the function to automatically put a period beside the cell in column E. If there is nothing in the cell in Column D, then I don't want there to have anything happen.

I want this to happen automatically in the worksheet... so I don't have to run a macro everytime. Thx

[JBeaucaire]

Place this macro in the SHEET MODULE so it is active all the time. It will watch for activity in column D. If any cell in column D is changed, it place a period in column E. If the change CLEARS a cell in column D, the column E period would be removed, too.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error Resume Next
For Each cell In Intersect(Target, Range("D:D"))
    Application.EnableEvents = False
    If cell <> "" Then
        cell.Offset(, 1) = "."
    Else
        cell.Offset(, 1) = ""
    End If
    Application.EnableEvents = True
Next cell

End Sub

[Xx7]

Jerry,

It seems to give me a run-time error anytime I enter info in columns other than D. Any way around this?

Thanks!

[JBeaucaire]

My bad, correction added above.

[Xx7]

Beautiful... works like a charm

[clng]

Dear Jerry,

Would it be better if we use intersect so that when the data gets huge, this will only run macro on the specific cell updated.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error Resume Next
If Not Intersect(Target, Range("D:D")) Is Nothing Then    

Application.EnableEvents = False
    If Activecell <> "" Then
        Activecell.Offset(, 1) = "."
    Else
        Activecell.Offset(, 1) = ""
    End If
    Application.EnableEvents = True

End If 


End Sub

Pls advice as I am also exploring.

Cheers,
CL

[JBeaucaire]

My macro in post #2 only operates on the cell(s) just changed. My design is set to watch all the cells you change, even if you do more than one cell at one time, and operate on the ones that are in column D. Did you notice the Intersect() reference already in my macro?

Your macro utilized Activecell references which I would not do.

[clng]

Hi Jerry,

I had the impression that the "For" loop command will run thru all cells. Thanks for pointing out the occassion that user may mass update cells using copy-paste actions.

Cheers,
CL

[JBeaucaire]

The FOR/NEXT loop runs based on controls you define. I defined my FOR as "all the cells just changed that intersect column D."

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[JBeaucaire]

As per forum rules, continue your discussion here, not in Private Messages.

Please post your question here;

[Xx7]

Trying to do this: If anything in column D, then put period in column E.

ALSO

If anything in column A or B, then put the current date in column I. (code below for this)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If .Count > 1 Then Exit Sub
            
            
            If Not Intersect(Range("A5:A2000"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 8).ClearContents
                Else
                    With .Offset(0, 8)
                        .NumberFormat = "dd-mmm-yy"
                        .Value = Date
                    End With
                End If
                Application.EnableEvents = True
            End If
            
            If Not Intersect(Range("b5:b2000"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 7).ClearContents
                Else
                    With .Offset(0, 7)
                        .NumberFormat = "dd-mmm-yy"
                        .Value = Date
                    End With
                End If
                Application.EnableEvents = True
            End If
            
            
        End With
    End Sub

When I use the above together with the below... they clash. Any way around this?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error Resume Next
If Not Intersect(Target, Range("D:D")) Is Nothing Then    

Application.EnableEvents = False
    If Activecell <> "" Then
        Activecell.Offset(, 1) = "."
    Else
        Activecell.Offset(, 1) = ""
    End If
    Application.EnableEvents = True

End If 


End Sub

[JBeaucaire]

I did not/do not/will not use an ACTIVECELL reference in a ws_change macro. Didn't we talk about this already?


There can only be one ws_change macro. Anything you want will need to be built into the one macro.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error Resume Next
Application.EnableEvents = False

For Each cell In Target
    If cell.Row >= 5 Then
        Select Case cell.Column
            Case 1, 2   'column A or B
                If cell <> "" Then
                    Range("I" & cell.Row).Value = Format(Date, "dd-mmm-yy")
                Else
                    Range("I" & cell.Row).Value = ""
                End If
            Case 4  'column D
                If cell <> "" Then
                    cell.Offset(, 1) = "."
                Else
                    cell.Offset(, 1) = ""
                End If
        End Select
    End If
Next cell

Application.EnableEvents = True
End Sub

[Xx7]

Gotcha... thank you!!!