Simplification - changing 2 cells at once

Question

I am trying to figure out how to simplify the code with no luck.

I managed to build a working code, which does the following: 1. If 'ja' is filled into cell 34 - in cell 35 the date appears - in cell 36 the username appears 2. If the cell is empty, the content is cleared

Do you have any tips / can help me out?

Thank you very much.

This is the code I have so far:

'show date
   If Target.Column = 34 Then
      Select Case Target
         Case "ja", "Ja":    Target.Offset(0, 1) = Date
      End Select
'show username
   If Target.Column = 34 Then
      Select Case Target
         Case "ja", "Ja":    Target.Offset(0, 2) = Application.UserName
      End Select
End If
End If

' clear contents
    Dim n As Long

    If Target.Column = 34 Then
        If IsEmpty(Cells(Target.Row, 34)) Then
               Range("AI" & Target.Row & ":AJ" & Target.Row).ClearContents
        End If
    End If
End Sub

score 0 · Accepted Answer · answered May 26 '20 at 09:57

0

I think this would do it:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Column = 34 Then
        Select Case Target
            Case "ja", "Ja"
                Target.Offset(0, 1) = Date 'show date
                Target.Offset(0, 2) = Application.UserName 'show username
            Case vbNullString 'Clear contents
                Range("AI" & Target.Row & ":AJ" & Target.Row).ClearContents

        End Select
    End If

End Sub

answered May 26 '20 at 09:57

Damian

5,152
1
10
21

Should this procedure run on the `SelectionChange` event? – Variatus May 26 '20 at 10:28
Well, I don't know which event were you running, it would run on your event. – Damian May 26 '20 at 10:40

Simplification - changing 2 cells at once

1 Answers1