Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

[JLoewen]

I have a worksheet change event in development that is already large enough it visibly slows my system down. The change event iterates through about 500 rows of data based on the user entries. I am using the change event to guide the user in what entries are valid vs. invalid with colorcoding. There are several dozen comparisons and evaluations being done on each row. The total effect is that it takes about 4 seconds for the VBA to complete processing each time new data is entered.

Each row is evaluated independent of every other row so every time this process runs only 1 row is actually being changed. The other 499 are being processed unnecessarily.

Is there a way to setup a change event so that it targets only a particular row or range of cells instead of the entire worksheet?

Regards,

 

[Norie]

The change event is passed an argument, Target, which is the range that has been changed.

If you only want to process what's been changed use that argument in your code.

 

[offthelip]

he change event iterates through about 500 rows of data based on the user entries.

Are you iterating down the worksheet ?, because if you are you could speed it up enormously by using variant arrays to do the checking. On your worksheet change event load the enitre worksheet into memory and then iterate through the variant array.
for example

Private Sub Worksheet_Change(ByVal Target As Range)

inarr = Range(Cells(1, 1), Cells(500, 200))
for i = 1 500
 if inarr ( i ,1) = "garbage" then
 ' do something
end if
next i

End Sub

 

[My Aswer Is This]

Show us your change event script.

 

[JLoewen]

I am iterating through a 33x500 cell matrix.

Here is the code:

 Private Sub worksheet_change(ByVal Target As Range)
 
  
 For i = 0 To 500
  'A bunchof conditional statements
Next i




End Sub

 

[Marcelo Branco]

I think it's possible to do what you want, but we need more details. Anyway, see if this example helps
Say you have data in columns A1:E10 and every time the user changes a value in A1:A10 you want to check if there is an empty cell in the correspondent row (Bx:Ex)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range
    
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        For Each rCell In Range("B" & Target.Row & ":E" & Target.Row)
            If rCell = "" Then
                rCell.Interior.Color = vbYellow
            Else
                rCell.Interior.Color = xlNone
            End If
        Next rCell
    End If            
End Sub

M.

 

[Norie]

Are you referring to Target in the code anywhere?

If you aren't then it kind of hard to see why you would use a change event.

 

[JLoewen]

Here is a larger snapshot of my code. I am not including the whole thing because it just gets cumbersome to look at.

I have been using the Range.offset method of specifying and cells. I am new to VBA writing (about two weeks in so far) and do not know if that is the best way to go about this.

My data inputs run from Column O to column AG. Everything before column O is driven by the spreadsheet. My user manipulable section runs from O15:AG515. My complete matrix runs from B15 to AG515.

I would like to set this up so that if the user makes a change on row 25 then it only runs the comparisons and changes colors for row 25.

Private Sub worksheet_change(ByVal Target As Range)
 
    blue = RGB(217, 225, 242) 'sets the color for cells that allow input.
    ggray = RGB(150, 150, 150) 'sets color gray, applied to cells that conditionally allow input when they are not accepting.
    yellow = RGB(255, 255, 0) 'sets cell color to yellow, applied when a room name does not resolve.
    red = RGB(255, 150, 150)
    blank = RGB(255, 255, 255)
    override = RGB(255, 238, 183) 'a peach color for cells where the user inputs an override.
    green = RGB(150, 255, 150)
  
 For i = 0 To 500
    
    
    
    'This set controls the color status of the Min Required Exhaust cells, Plan return and Plan Exhaust cells, HEPA cells (M, N, R & S)
    If Range("M15").Offset(i) = "" Then                                         'A quick check to see if the Min Req'd Exhaust column is empty
        m = 0                                                                   'Provides a value for the comparison in later statements
        Else: m = Range("M15").Offset(i)
        End If
        
    If Range("G15").Offset(i) = "No" Then
        If Range("N15").Offset(i) = "YES" Then                                          'If HEPA filtration is allowed
            Range("N15").Offset(i).Interior.Color = blue
            Range("S15").Offset(i).Interior.Color = override
            
            If Range("R15").Offset(i) = "" Then                                     'Blank exhaust cell turns red.
                    If m <> 0 Then
                        Range("M15, R15").Offset(i).Interior.Color = red
                        Else
                            Range("R15").Offset(i).Interior.Color = red
                            Range("M15").Offset(i).Interior.Color = blank
                        End If
                ElseIf (Range("R15").Offset(i) + Range("S15").Offset(i)) >= m Then              'Check to see if exhaust + return exceed required exhaust
                    Range("R15").Offset(i).Interior.Color = blue
                    Range("M15").Offset(i).Interior.Color = blank
                    Else
                        Range("M15, R15").Offset(i).Interior.Color = yellow                 'Otherwise they are both yellow
                End If

Next i

 

[JLoewen]

Norie,

I am not using Target yet though it seems I should be.

 

[My Aswer Is This]

Here is a example of a sheet change event script.
If you enter the word George in column A
This script will run:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "George" Then
    With Target
        .Offset(, 1).Value = "Me"
        .Offset(, 3).Value = "You"
        .Offset(, 5).Value = "Us"
        .Offset(, 7).Value = "All Of Us"
    End With
End If
End If
End Sub