modify data based on range

[daveyc18]

i want a macro that loops from column K in sheet I'll call "ABC" and checks the values against a data range called "filter" (which is in another sheet called "filter criteria")

if it finds a row in column K that's also found in the data range "filter" (eg the number 005), then it'll zero out the value in column J of sheet "abc," highlight the cell zeroed out cell in yellow, and in column M to display "new issue"


here's my code right now, but i prefer the data range to be on the spreadsheet itself as it can change from time to time (dont want to go into the code and modify it)

'zeros out new issue values


For i = 3 To finalrow


If Range("K" & i).Value = "005" Or Range("K" & i).Value = "006" Or Range("K" & i).Value = "044" Or Range("K" & i).Value = "046" Or Range("K" & i).Value = "122" Or Range("K" & i).Value = "170" Or Range("K" & i).Value = "171" Or Range("K" & i).Value = "172" Or Range("K" & i).Value = "173" Or Range("K" & i).Value = "174" Or Range("K" & i).Value = "175" Or Range("K" & i).Value = "176" Or Range("K" & i).Value = "177" Or Range("K" & i).Value = "178" Or Range("K" & i).Value = "179" Or Range("K" & i).Value = "180" Or Range("K" & i).Value = "181" Or Range("K" & i).Value = "182" Or Range("K" & i).Value = "183" Or Range("K" & i).Value = "190" Or Range("K" & i).Value = "191" Or Range("K" & i).Value = "192" Or Range("K" & i).Value = "193" Or Range("K" & i).Value = "194" Or Range("K" & i).Value = "226" Then


'zero out  mv
Range("j" & i) = 0


'highlight the 0


Range("j" & i).Interior.Color = 65535


'put new issue in column M
Range("m" & i) = "New Issue"


End If


Next

 

[Rijnsent]

Hi Daveyc18,
try something like this, it's basically a loop in a loop. More about that, check out this VBA course chapter - https://www.excel-pratique.com/en/vba/loops_continued.php
Cheers,
Koen

Sub ZeroOut()
'zeros out new issue values

'Set variables
Set ShtABC = Worksheets("ABC")
FilterValues = Range("Filter").Value
Set DataRngStart = ShtABC.Range("K3")
Set DataRngEnd = ShtABC.Range("K3").End(xlDown)

'Loop through values
For i = DataRngStart.Row To DataRngEnd.Row
    
    'For each value, loop through filter
    FilterFound = False
    For Each f In FilterValues
        If DataRngStart.Offset(i - DataRngStart.Row, 0).Value = f Then
            'Filter found, exit loop
            FilterFound = True
            Exit For
        End If
    Next f
    
    If FilterFound = True Then
        'zero out  mv
        ShtABC.Range("j" & i) = 0
        'highlight the 0
        ShtABC.Range("j" & i).Interior.Color = 65535
        'put new issue in column M
        ShtABC.Range("m" & i) = "New Issue"
    End If
    
Next

'Clean set variables
Set ShtABC = Nothing

End Sub