CODEWITHSUNDEEP
vbaexcel
LiamD
LiamD

Reputation: 1

excel vba range offset range relative

I am trying to format a region in a worksheet.

I am using the Worksheet_Change so that it always updates on a change.

My goal is to look at all the cells in a region. If the current cell is empty and the cell to the left has numerical value 0, then I want to enter text "N/A" in the current cell.

My attempt is failing as the Offset cannot be used.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim updateCells As Range
    Dim myCell As Range

    Set updateCells = Range("B1:M1000")

    For Each myCell In updateCells
        ' NEXT LINE WRONG!!
        If myCell.Offset(0, -1).Value = 0 Then
            myCell.Interior.ColorIndex = 4
        End If

    Next

End Sub

Any guidance would be appreciated.

Upvotes: 0

Views: 437

Answers (2)

user3598756
user3598756

Reputation: 29421

I'd go this way

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, myRng As Range

    On Error GoTo ExitSub  '<--| be sure to exit sub in a managed way and restore events handling
    Set myRng = Intersect(Target, Range("B1:M1000")).SpecialCells(xlCellTypeBlanks) '<--| get blank changed cells belonging to relevant range
    Application.EnableEvents = False '<--| turn rvents handling ofF
    For Each rng In myRng '<--| loop through filtered range only
        If Not rng.Offset(0, -1).Value = "" And rng.Offset(0, -1).Value = 0 Then
            rng.Value = "N\A"
            rng.Interior.ColorIndex = 4
        Else
            rng.Interior.ColorIndex = -4142
        End If
    Next

    ExitSub:
    Application.EnableEvents = True '<--| turn events handling on
End Sub

Upvotes: 1

user6432984
user6432984

Reputation:

I would test if any the Target's cells are in the Range("B1:M1000"). You should always turn off events when changing values on the ActiveSheet from the Worksheet_Change event. 

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    Dim r As Range
    For Each r In Target.Cells
        If Not Intersect(r, Range("B1:M1000")) Is Nothing Then
            If r.Value = "" And Not r.Offset(0, -1).Value = "" And r.Offset(0, -1).Value = 0 Then
                r.Value = "N\A"
                r.Interior.ColorIndex = 4
            Else
                r.Interior.ColorIndex = -4142
        End If
    Next

    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions