excel vba range offset range relative

Question

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.

Offset(-1, 0) is looking at the cell above not to the left Offset(0,-1) — Scott Craner
– Scott Craner, Commented Nov 3, 2016 at 20:28
Hi Scott, thanks for the feedback. I copied it incorrectly. However, even this change is wrong. Is my setup and loop over the range correct? — LiamD
– LiamD, Commented Nov 3, 2016 at 21:16

user6432984 · Accepted Answer · 2016-11-03 21:32:38Z

1

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

answered Nov 3, 2016 at 21:32

user6432984

Sign up to request clarification or add additional context in comments.

Comments

user3598756 · Accepted Answer · 2016-11-04 06:03:11Z

1

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

edited Nov 4, 2016 at 6:03

answered Nov 3, 2016 at 22:49

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

2 Comments

Slai Over a year ago

nice, but I would recommend On Error before .SpecialCells

user3598756 Over a year ago

@Slai, you are right. That also makes subsequent "IF myRng..." unnecessary. Edited

Collectives™ on Stack Overflow

excel vba range offset range relative

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related