CODEWITHSUNDEEP
excelvba
xyz
xyz

Reputation: 2300

Worksheet_change not working when cell content changes via VBA but does manually

I am trying to color the background of all cells in column B whose content has changed via VBA.

The background changes if I manually update the cells but not when it changes via VBA. I can not get why it is not changing with the VBA.

In the worksheet module for the sheet called OriginalData I have

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Dim nName As String, nEmail As String

    Application.EnableEvents = False

    For Each c In Target
      If c.Column = 2 And Target <> "" Then
         c.Interior.Color = RGB(255, 255, 0)
      End If
    Next c

    Application.EnableEvents = True

End Sub

I am updating the Column 2 on OriginalData with

Sub FindReplace_Updated_UnMatched_NAMES_Original_Prepperd_2()
    Dim FindValues As Variant
    Dim ReplaceValues As Variant
    Dim wsFR As Excel.Worksheet
    Dim wsTarget As Excel.Worksheet
    Dim lRow As Long
    Dim i As Long

    Sheets("Updated_UnMatched").Select

    Set wsFR = ThisWorkbook.Worksheets("Updated_UnMatched")
    Set wsTarget = ThisWorkbook.Worksheets("OriginalData")

             lRow = wsFR.Range("C" & wsFR.Rows.Count).End(xlUp).Row
       FindValues = wsFR.Range("C1:C" & lRow).Value
    ReplaceValues = wsFR.Range("D1:D" & lRow).Value


    With wsTarget
      If IsArray(FindValues) Then
         For i = 2 To UBound(FindValues)
                 .Columns("B:B").Replace FindValues(i, 1), ReplaceValues(i, 1), xlWhole, xlByColumns, False
         Next i
      Else

      End If
    End With

End Sub

Upvotes: 2

Views: 8509

Answers (2)

Reza Aghaei
Reza Aghaei

Reputation: 125187

When there is some errors during event handler execution, it doesn't work properly for next times. You can find and fix the errors and it will work properly.

As a quick fix, you can do these steps:

  1. Add On Error Resume Next at the beginning of Worksheet_Change to prevent errors make your code stop working.
  2. Save your workbook in a macro enabled format and reopen it enabling active content.
  3. Run macro and it will work properly.

I tested your code and it worked for me in Excel 2013.

It is strongly recommended to fix your errors instead of hiding them using On Error Resume Next.

Upvotes: 1

user4039065
user4039065

Reputation:

You likely errored out on Target <> "" and got stuck with Application.EnableEvents = False environment state.

First, go to the VBE's Immediate Windows (Ctrl+G) and enter the command Application.EnableEvents = True. While in the VBE, make this modification to your code for multiple Target cell counts.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Dim nName As String, nEmail As String

    Application.EnableEvents = False

    For Each c In Target
      If c.Column = 2 And c.Value <> "" Then  '<~~ c <> "", not Target <> ""
         c.Interior.Color = RGB(255, 255, 0)
      End If
    Next c

    Application.EnableEvents = True

End Sub

That should be enough to get you going.

Upvotes: 2

Related Questions