0

I want to write a VBA code that applies to 1000 rows in excel. And in each row, once I change the cell value in column G to No, the respective row (i.e. column H-Z) will fill in with X automatically.

I tried to write the code below, but it doesn't work all the time, only appear occasionally, can someone help me with this, please?

Private Sub Worksheet_Change(ByVal Target As Range)
    'MsgBox Target.Address
    If Not Application.Intersect(Range("B1:B100"), Range(Target.Address)) Is Nothing Then
        Call Change_value
    End If
End Sub

Sub Change_value()
    If ActiveCell.Value = "No" Then
        Cells(ActiveCell.Row, 4).Value = "Ok"
    End If
End Sub
mtekin
  • 19
  • 3
Aiden
  • 5
  • 2
  • Yes there is, but first please take the time and read [this](https://stackoverflow.com/help/how-to-ask) – horst Oct 23 '20 at 13:18
  • 1
    Also [worth a glance](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Nov 10 '20 at 05:46
  • 1
    Also remember if user pastes `No` in say `G25:G35` and if you want `X` in all that rows then you will have to handle that differently as well :) – Siddharth Rout Nov 10 '20 at 05:54
  • @Siddharth Rout - excellent tips in the link! I won't amend the code in the answer just yet, I'll wait to see if Aiden returns to SO. Hopefully, they'll follow your link and learn something, as I did. –  Nov 10 '20 at 06:28
  • @kevin9999 Oh my comment was not directed at your answer so no need to modify becuase I posted a link. I just gave a link to Aiden for further reading. :) – Siddharth Rout Nov 10 '20 at 06:32
  • @Siddharth Rout - yes, understood, just wanted to show my appreciation of the effort you put in helping others here on SO. FYI I upvoted the answer in the link you provided, and will use those principles in future coding of Private Subs. Thanks again :) –  Nov 10 '20 at 06:36
  • No worries @kevin9999 :) – Siddharth Rout Nov 10 '20 at 06:39

1 Answers1

1

@Aiden – here’s what’s wrong with your question:

It’s contradictory.

You say you want the code to apply to “1000 rows in excel” but your code refers to 100 rows only (“B1:B100”)

You say you want it to trigger the sub when a cell in “column G” changes – but your code refers to column B

As such, it’s difficult for people to understand what your intention really is

Your code should be formatted as code – by which I mean that when you enter your question, you highlight all your code and select {} to mark it as such

Having said that, I’ll give you a solution on the assumption that you did take @horst advice and read https://stackoverflow.com/help/how-to-ask.

You don’t need to call a sub from within a Private Sub when the task is as simple as this one. Assuming you meant Column G, and 1000 rows, this should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("G1:G1000"), Target) Is Nothing Then

If Target.Rows.Count > 1 Then Exit Sub

If Target.Value = "No" Then
    Range(Target.Offset(0, 1), Target.Offset(0, 19)).Value = "X"
End If

End If
End Sub