CODEWITHSUNDEEP
excelvba
Gilalar
Gilalar

Reputation: 105

Excel VBA-code to alter a cell if a line has been edited

I'm doing some data validation work and basically, whenever I alter a line in the data I also need to write "Yes" in J-column on the same line. I figured this should be automatizable, but I had some trouble with the code.

What I want it to do is to check for changes in C-H rows, and if there's a numeral (0-99) on the B-column, then replace the text on the J-column of the same line with "Yes" (without the quotation marks). The "numeral"-part can be abbreviated to having length of 1-2 (not 0 and not more) in this case.

Here's what I have thus far, but I can't seem to figure out how to do the combination of absolute and relative reference, i.e. "check B of the active row" or "alter J of the active row" (that is to say, none of the codes I've tried thus far have been valid according to VBA; I have very little VBA experience so this code feels alien to me):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:H")) Is Nothing Then Exit Sub
        If Not Len("B" & ActiveCellRow) = "1" Then
        If Not Len("B" & ActiveCellRow) = "2" Then
        Exit Sub
    End If
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
        "J" & ActiveCellRow = "Yes"
    .EnableEvents = True
    .ScreenUpdating = True
End With

Upvotes: 0

Views: 43

Answers (1)

Tim Williams
Tim Williams

Reputation: 166456

You can do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, c As Range, v

    'in our range of interest?
    Set rng = Application.Intersect(Target, Me.Range("C:H"))

    If Not rng Is Nothing Then
        'get the corresponding cells in ColB
        For Each c In Application.Intersect(rng.EntireRow, Me.Range("B:B")).Cells
            v = c.Value
            'flag required?
            If Len(v) > 0 And Len(v) < 3 And IsNumeric(v) Then
                c.EntireRow.Cells(1, "J").Value = "Yes"
            End If
        Next c
    End If
End Sub

Upvotes: 1

Related Questions