Check list macro

[tstigman]

I'm still a beginer in programming in general, Ive been trying to get a macro to check a list of numbers A7: Axx

I was able to do it with conditional formatting and creating a list in another column however that is not what i want to accomplish. I want the macro to check A7:Axx against a list of "restricted numbers" worked into the macro. so if the worksheet changes the macro is still usable with the new data.

 

[cjcobra]

You could start at the first cell, and use OFFSET to go down one number at a time and check to see if that number is in your list. Then you LOOP this for however many lines you've got.

 

[tstigman]

I understand that part and it works all the way down however my delima is this is an example of how its set up.

column A: Column B:
A B
B F
C G
D A


I want to remove column B from the sheet. and put the variables in some sort of "list" within the macro so as new data is downloaded im able to still identify the variables that are in Column B against new data in Column A

 

[Ralajer]

The following will but the values of column B into an array which can then be accessed via its position colBVals(3) would = "G" from your example.

Sub test()
    Dim rng As Range
    Dim ws As Worksheet
    Dim firstRow As Long, lastRow As Long
    Dim col As Long
    Dim i As Long
    Dim colBVals() As String
    
    col = Columns("A").Column   '// Column that contains data
    firstRow = 7                '// First row of interest
    
    Set ws = Worksheets("Sheet1")
    
    With ws
    
    '// Last row in column specified in col that contains anything
    lastRow = .Cells(Rows.count, col).End(xlUp).Row
    
    Set rng = .Range(Cells(firstRow, col), .Cells(lastRow, col))
    
    ReDim colBVals(1 To rng.count)

    For i = 1 To rng.count
        '// Store values in column B in variable
        colBVals(i) = rng.Cells(i, 1).Offset(0, 1).Value
    Next i
    
    End With

End Sub