modify data based on range

daveyc18

Well-known Member
Joined
Feb 11, 2013
Messages
706
Office Version
  1. 365
  2. 2010
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)

Code:
'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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top