Compare Ranges with vba

[bjc124]

I'm looking for a solution to compare 2 ranges of data. I'd like to find a vba solution if possible.

I'd like to compare the data in F6:J50 with the data in A6:E50 and if there is anything missing or that doesn't match be highlighted in the Light Red (Bad Cell Format) color and if there is anything in A6:E50 that isn't found in F6:J50 be highlighted in the neutral Yellow cell format.

If I'm going about this all wrong and there's another much simpler way of executing this by all means please set me straight. I've included a sample spreadsheet with example data for someone to play with.

I did experiment with the compare cells function of Kutools and while it worked and did pretty much what I wanted the process was extremely slow.

[AlphaFrog]

You can do this with Conditional Formatting.

Select A6:F50. The formulas will automatically adjust for each selected cell.

Conditional Format Rule 1 Formula (Yellow)

Please Login or Register  to view this content.

Conditional Format Rule 2 Formula (RED)

Please Login or Register  to view this content.

You could do the same for the F:J section by adapting the same formulas.

[bjc124]

It seems to be working pretty good, but It's highlighting the opposite. All the values that match are highlighted red and when I make a change in F6:J50 to force one to not match it formats the cell back to white. Is there any way to flip flop this where the non matching cells are the ones highlighted red.

[AlphaFrog]

Conditional Format Rule 2 Formula (RED)

Please Login or Register  to view this content.

[bjc124]

Beautiful! I'm thankful for the help and the quick reply!