Excel VBA onChange Event

Question

I'm trying to fire an onChange event when value entered to column A.

Now I want this, if I enter any value from Column A to Column AS, the event will fire and if I remove any value from same columns it will work as Code is written.

Also if I copy and paste a multiple data it's not working, also if I'm removing the multiple data it's not working.

Can anyone help on this? Below is the code.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim currentRow As Integer

    If Not Intersect(Target, Columns("A")) Is Nothing Then

        If Target.Value <> "" Then
            currentRow = Target.Row
            Target.Parent.Range("A" & currentRow & ":AS" & currentRow).Interior.ColorIndex = 15
            Target.Parent.Range("A" & currentRow & ":AS" & currentRow).Borders.LineStyle = xlContinuous
        End If

        If Target.Value = "" Then
            currentRow = Target.Row
            Target.Parent.Range("A" & currentRow & ":AS" & currentRow).Interior.ColorIndex = 0
            Target.Parent.Range("A" & currentRow & ":AS" & currentRow).Borders.LineStyle = xlNone
        End If

    End If
End Sub

Selecting multiple cells will mean Target.Value is an array. If Target.Value = "" Then will always be false when Target is an array. — Automate This
– Automate This, Commented Nov 22, 2018 at 5:48
As a side note, use Cells arguments instead of constructing range address from string:With Target.Parent: .Range(.Cells(currentRow, "A"), .Cells(currentRow, "AS")): End With. This is more readable. Or: Target.Parent.Cells(currentRow, "A").Resize(, 19). This is more concise! 😉 — JohnyL
– JohnyL, Commented Nov 22, 2018 at 8:07

Automate This · Accepted Answer · 2018-11-22 06:17:59Z

Target.Value only has a value if a single cell is selected. If you select more than one cell it becomes an array and your If statement will always evaluate to False.

Here is one way to change your code. I was in a bit of a hurry so it could probably be done much better but should get you started.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Columns("A")) Is Nothing Then
        If Application.WorksheetFunction.CountA(Target) = 0 Then
            ' Empty Range
            For Each rw In Target.Rows
                Target.Parent.Range("A" & rw.Row & ":AS" & rw.Row).Interior.ColorIndex = 0
                Target.Parent.Range("A" & rw.Row & ":AS" & rw.Row).Borders.LineStyle = xlNone
            Next rw
        Else
            ' Not Empty
            For Each rw In Target.Rows
                Target.Parent.Range("A" & rw.Row & ":AS" & rw.Row).Interior.ColorIndex = 15
                Target.Parent.Range("A" & rw.Row & ":AS" & rw.Row).Borders.LineStyle = xlContinuous
            Next rw
        End If
    End If
End Sub

Collectives™ on Stack Overflow

Excel VBA onChange Event

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related