Array with If Then

[papadogiannakis]

Hello, I have a slight problem.

I want a macro that can check if a value on sheet 1, column B matches one of multiple values in sheet 2, column B on and if so make the cell on the first sheet turn green.

I need this to compare batch codes and to quickly see what ones have been approved.
Only I have no idea where to start. The most I have worked with VBA is to create macro's that print things, haha.

I did some research and i think i need the Array functions and the If-Then function.

This is what i came up with, but it doesnt work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Range("B8: B30")
    If cell.Value = Sheets("Batch Data").Range("A2:A30") Then
    cell.Interior.Color = XlRgbColor.rgbLightGreen
End If
Next

End Sub

As you can see I am really new at this and was made the 'computer guy' because i game a bit, haha
So help and explanation is greatly needed and appreciated!

Many thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
You can do this with conditional formatting.
Select B8:B30 > Home Tab > Conditional format . New Rule > Use a formula > =ISNUMBER(MATCH(B8,'Batch Data'!A$2:A$30,0)) > select format > Ok

 

[papadogiannakis]

Well, that works! Kind of. Had te replace the comma's with whatever these ' ; ' are called in English
Just out of curiosity, would it be possible to do it with a macro?
Even if I don't understand much about VBA, I do want to learn!

Thanks for the quick reply and help!

 

[Fluff]

How about

Sub ColourCells()
   Dim Cl As Range
   Dim Res As Variant
   
   For Each Cl In Range("B8:B30")
      Res = Application.Match(Cl.Value, Sheets("Batch Data").Range("A2:A30"), 0)
      If Not IsError(Res) Then
         Cl.Interior.Color = XlRgbColor.rgbLightGreen
      Else
         Cl.Interior.Color = xlNone
      End If
   Next Cl
End Sub