Compare cell value to fixed array value

[detweiler]

Hope my ask actually makes sense...

I would like to have a macro that would compare the values in a column, let's say column J, to a fixed list or an array written in the macro, and then highlight the cell or change the text color of the value that doesn't match what's in the list?

Any suggestions on how to set this up are appreciated.

 

[James006]

Hi,

You can design your macro around Application.Match ...

Hope this will help

 

[detweiler]

James,

Thanks, but I'm not quite that good at this yet. Found a Microsoft article about how that works and, unfortunately, got a little more lost in the weeds.

 

[Fluff]

Why not just have your list in a sheet & use conditional formatting?

 

[detweiler]

I've considered having a static workbook with the multiple data sets as tables for location reference. Would still like to automate the process to do the compares and highlight/indicate the differences/discrepancies.

Might that be a more sound method to work towards then?

 

[Fluff]

Entirely depends on what you are trying to do, which is not very clear to me.
In your OP you mentioned

compare the values in a column, let's say column J, to a fixed list or an array written in the macro

but you are now talking about

multiple data sets as tables for location reference

 

[detweiler]

Yes, apologies. But as the case in editing my responses, I removed the mention that what I'm working on is but one step in the over all process that could potentially be mirrored as the other data sets a re structured similarly.

 

[Fluff]

Unless your values never ever change I would have them listed on a sheet, rather than hard code them in a macro.
That way anyone can modify the list, rather than you having to modify the code & then check that it still works.

 

[detweiler]

Yep, I've thought about that, and for now the values are pretty much static with the occasional update. I am left with one of three choices to work with the data each time it gets exported is into a new workbook,

1. Import the worksheet with the listed values into the new workbook;
2. Configure the macro to reference the workbook.worksheet.tableName; or,
3. Maintain the static values in the macro, literally being available a click away.

My pref, I'm sure you guessed, is option 3.

It sucks knowing what you don't know and having to reach out because you can't find the pieces you need to frankenvba something together. And, sure, I may be over complicating it just a smidge or by quite a but, but I prefer to work at the issue as hard as I can now so I can enjoy the laziness of just clicking a button know the macro is doing all of the heavy lifting.

 

[Fluff]

If you're happy hard coding it,then you could use something like

Sub detweiler()
   Dim Ary As Variant
   Dim i As Long
   Dim Cl As Range
   
   Ary = Array("Peter", "Paul", "Mary", "Ginger", "Eric", "Jack")
   With CreateObject("scripting.dictionary")
      .CompareMode = vbTextCompare
      For i = 0 To UBound(Ary)
         .Item(Ary(i)) = Empty
      Next i
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then Cl.Interior.Color = 45678
      Next Cl
   End With
End Sub