Excel VBA Change Value in another column and row in range

Question

I have problem in my code, i need to change in another column - row

I tried to built macro but it's dosn't work with that.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Target, Range("A6:U1000"))

If xRg Is "YES" Then Exit Sub
Range("G" & Target.Row).Value = "CHECK"
End Sub

When in column N6:N1000 is "YES" in Column G change value to "Check" and all row A6 for example to U1000 is in color red

Please, delete line `On Error Resume Next` because it *hides* errors, but they still occur, and execute your code. — Foxfire And Burns And Burns, Apr 23 '19 at 09:54
Currently the code will `Exit Sub` once a value of `YES` is encountered. Should that read `<> "YES"` — MiguelH, Apr 23 '19 at 09:56
`If xRg.Value = "YES" Then Exit Sub` Is this what you are trying? Also since you are working with `Worksheet_Change`, you may want to se [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) — Siddharth Rout, Apr 23 '19 at 09:58
Yes @SiddharthRout i'm trying to do this but it's doesn't work with that. Is mismatch error -13 — FighterWks, Apr 23 '19 at 10:03
You have to use `If Not Intersect(Target, Range("A6:U1000")) Is Nothing Then`. Please once again read the link that I gave above. — Siddharth Rout, Apr 23 '19 at 10:06

score 0 · Accepted Answer · answered Apr 23 '19 at 10:57

I can't quite understand what you're trying to achieve here, but hopefully the below will be doing roughly what you need. Try it and let me know if it doesn't behave the way you hope.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim xRg As Range, cl as Range
    Set xRg = Intersect(Target, Range("A6:U1000"))
    If Not xRg Is Nothing Then
        For Each cl In xRg.Cells
            If cl.Value = "YES" Then Range("G" & cl.Row).Value = "CHECK"
        Next
    End If
    Application.EnableEvents = True
End Sub

Excel VBA Change Value in another column and row in range

1 Answers1