Detect change for cells with a specific value-CodePudding

Whenever a specific type of cell has it's value changed, I would like to clear the cell below it. Since this value can be any string, I'm searching for it by its label one column to the left and finding all instances through that label using this

Function FindAll(rng As Range, What As Variant, Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole, Optional SearchOrder As XlSearchOrder = xlByColumns, Optional SearchDirection As XlSearchDirection = xlNext, Optional MatchCase As Boolean = False, Optional MatchByte As Boolean = False, Optional SearchFormat As Boolean = False) As Range
    Dim SearchResult As Range
    Dim firstMatch As String
    With rng
        Set SearchResult = .Find(What, , LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
        If Not SearchResult Is Nothing Then
            firstMatch = SearchResult.Address
            Do
                If FindAll Is Nothing Then
                    Set FindAll = SearchResult
                Else
                    Set FindAll = Union(FindAll, SearchResult)
                End If
                Set SearchResult = .FindNext(SearchResult)
            Loop While Not SearchResult Is Nothing And SearchResult.Address <> firstMatch
        End If
    End With
End Function

Then I try to add a worksheet_change to all instances like this


Private Sub Worksheet_Change(ByVal Target As Range)

 Dim SearchRange     As Range, SearchResults As Range, rng As Range

    Dim ws              As Worksheet: Set ws = Sheets("SHEET 1")

    Set SearchRange = ws.UsedRange

    Set SearchResults = FindAll(SearchRange, "TESTVALUE")


   If SearchResults Is Nothing Then
        'No match found
    Else
        For Each rng.Offset(1, 0) In SearchResults
             If Not Application.Intersect(rng.Offset(1, 0), Range(Target.Address)) Is Nothing Then
             
             ' Display a message when one of the designated cells has been
    ' changed.
    ' Place your code here.
            rng.Offset(1, 1).ClearContents
            MsgBox ("A")
            
            End If
        Next
    End If

End Sub

Nothing happens when I change the cell though including MsgBox

EDIT: Added image for clarification

When the user changes the dropdown for category, the dropdown for subcategory should be cleared.

CodePudding user response：

This is an easier way to do it I think:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    
    'set some limit for the size of change you want to handle
    If Target.Cells.CountLarge > 100 Then Exit Sub
    
    On Error GoTo haveError           'to make sure events turned back on
    Application.EnableEvents = False  'don't re-trigger the handler
    
    For Each c In Target.Cells        'check each cell in the changed range
        If c.Column > 1 Then
            Select Case c.Offset(0, -1).Value
                Case "Select Category:"
                    c.Offset(1, 0).ClearContents
                Case "Select Subcategory:"
                    'something else here...
            End Select
        End If
    Next c
    
haveError:
    Application.EnableEvents = True
End Sub