Intersect Code not working Excel VBA

[grabrail]

Hi

I have some code, and part of it is not working.

The code is designed to look for changes on my worksheet, and if identified, take a certain action. There are a number of different cells being checked for different things, and slightly different actions being taken for each.

Code is as follows

Private Sub Worksheet_Change(ByVal Target As Range)

'Dim afimref As String

    
    If Not Intersect(Target, Range("E13:E27,E29:E39,J13:J35,J37:J39")) Is Nothing Then
        
        If Target.Value = "DF" Then
            Inspection.Show
        End If
        
    
    'Antifreeze Check
    ElseIf Not Intersect(Target, Range("E121")) Is Nothing Then
        
        If Target.Value < 50 Then
        
        
        Worksheets("VI Sheet").Range("A50").Select
        'get next blank cell
            While ActiveCell.Value <> ""
                ActiveCell.Offset(1, 0).Range("A1").Select
            Wend
            'input results
            
            'Check No
            ActiveCell.FormulaR1C1 = "47"
            ActiveCell.Offset(0, 1).Range("A1").Select
            
            'IM ref.
            ActiveCell.FormulaR1C1 = "0"
            ActiveCell.Offset(0, 1).Range("A1").Select
            
            'Defect found
            ActiveCell.FormulaR1C1 = "Antifreeze low"
            ActiveCell.Offset(0, 5).Range("A1").Select
            
            'Serviceable
            ActiveCell.FormulaR1C1 = "S"
            ActiveCell.Offset(0, 3).Range("A1").Select
            
                        
            'Defect Sources
            ActiveCell.FormulaR1C1 = "FW"
            ActiveCell.Offset(0, -2).Range("A1").Select
            
             
            ActiveCell.FormulaR1C1 = "0"
        
        End If
       
       
        'Brake Test check
        ElseIf Not Intersect(Target, Range("G117")) Is Nothing Then
            
            If Target.Value = "Fail" Then
            
            Worksheets("VI Sheet").Range("J37").Select
            Worksheets("VI Sheet").Range("J37").Value = "DF"
            End If
            
            
        ElseIf Not Intersect(Target, Range("G118")) Is Nothing Then
        
            If Target.Value = "Fail" Then
            
            Worksheets("VI Sheet").Range("J38").Select
            Worksheets("VI Sheet").Range("J38").Value = "DF"
            End If
            
        ElseIf Not Intersect(Target, Range("G119")) Is Nothing Then
            
            If Target.Value = "Fail" Then
            
            Worksheets("VI Sheet").Range("J39").Select
            Worksheets("VI Sheet").Range("J39").Value = "DF"
            End If
            
            
            
        'Lubrication Check
        ElseIf Not Intersect(Target, Range("E125")) Is Nothing Then
        
            If Target.Value = "Excessive" Or Target.Value = "Inadequate" Then
        
        
            Worksheets("VI Sheet").Range("A50").Select
            
            'get next blank cell
            While ActiveCell.Value <> ""
                ActiveCell.Offset(1, 0).Range("A1").Select
            Wend
            
            'input results
            
            'Check No
            ActiveCell.FormulaR1C1 = "47"
            ActiveCell.Offset(0, 1).Range("A1").Select
            
            'IM ref.
            ActiveCell.FormulaR1C1 = "0"
            ActiveCell.Offset(0, 1).Range("A1").Select
            
            'Defect found
            ActiveCell.FormulaR1C1 = "Lubrication " & Target.Value
            ActiveCell.Offset(0, 5).Range("A1").Select
            
            'Serviceable
            ActiveCell.FormulaR1C1 = "S"
            ActiveCell.Offset(0, 3).Range("A1").Select
            
                        
            'Defect Sources
            ActiveCell.FormulaR1C1 = "FW"
            ActiveCell.Offset(0, -2).Range("A1").Select
            
             
            ActiveCell.FormulaR1C1 = "0"
            End If
            
            
            'Tacho Expiry Check


         ElseIf Not Intersect(Target, Range("G128")) Is Nothing Then
        
            If Target.Value = "Expired" Then

            Worksheets("VI Sheet").Range("A50").Select
         
            'get next blank cell
            While ActiveCell.Value <> ""
                ActiveCell.Offset(1, 0).Range("A1").Select
            Wend
        
            'input results
            
            'Check No
            ActiveCell.FormulaR1C1 = "47"
            ActiveCell.Offset(0, 1).Range("A1").Select
            
            'IM ref.
            ActiveCell.FormulaR1C1 = "0"
            ActiveCell.Offset(0, 1).Range("A1").Select
            
            'Defect found
            ActiveCell.FormulaR1C1 = "Tachograph Expired"
            ActiveCell.Offset(0, 5).Range("A1").Select
            
            'Serviceable
            ActiveCell.FormulaR1C1 = "S"
            ActiveCell.Offset(0, 3).Range("A1").Select
            
            'Defect Sources
            ActiveCell.FormulaR1C1 = "FW"
            ActiveCell.Offset(0, -2).Range("A1").Select
            
            'OCRS Score
            ActiveCell.FormulaR1C1 = "0"
            End If
                     
    End If
End Sub

All sections work, apart from the Tacho Expiry check section, the very last one. When the cells being checked in question are changed to expired, nothing happens.

I have a feeling that becuase those particular cells contain a formula, that check a different cell for a date, if the date is before today, the work expired is entered as the value for that cell.

Is it becuase the " ElseIf Not Intersect(Target, Range("G128")) Is Nothing Then" statement has "Is Nothing" and becuase there is a formula in the cell, it effectively is not nothing?

I admit I dont fully understand the intersect method at this point, I am using it as it was already in this workbook i inherited

 

[RoryA]

The Worksheet_Change event is not triggered by formula recalculations.

FYI, your current code is quite inefficient as all that selecting of cells is quite unnecessary, and you really ought to disable events while making changes to cells in a Change event, otherwise it will keep calling itself.

 

[grabrail]

The Worksheet_Change event is not triggered by formula recalculations.

FYI, your current code is quite inefficient as all that selecting of cells is quite unnecessary, and you really ought to disable events while making changes to cells in a Change event, otherwise it will keep calling itself.

Ah, so if i change the cell to data validation, list with Expired or Valid, and have the user manually select, this should fix my issue