Comparing columns and highlighting differences

Hi guys

First off! Thanks for an excellent forum. Been using it quite a bit during the last month and will continue to do so in the future.

My first post here is about comparing the contents of different columns in a sheet. There are two sets of data that needs to be compared.

The setup is as follows.

Every time a factory completes an order it is shipped to a warehouse. Everytime this happens they register the outgoing shipment using a date, reference number and an amount.

When the order is received at the warehouse they do a similar registration adding additional data as well.

And as stated above I would like to compare the reference numbers to see if they are present. If they match I should then look at the amount registered to each reference number.

If there are reference numbers present in either dataset my idea is to color the entire row red (or something like that).
If the reference number is present but the amount doesn't match this line should get a different color.

I have made a simple, not very good comparison macro, that writes directly to the sheet in a new column (not included in the current code).

But this is a cumbersome process and I would like to think that it could be done using arrays, the find function or something similar.

In the current code I have created two arrays containing the the information from the factory and the warehouse. Don't know if this is a step in the right direction or not but I'm hoping you guys could tell me

Kind regards
Michael

Michael - I can't really envisage what you're expecting the final sheet to look like. Can you elaborate, or add to your file?

If there are reference numbers present in either dataset my idea is to color the entire row red (or something like that).
If the reference number is present but the amount doesn't match this line should get a different color.

The reference numbers appear to be on the same line so should they be red or another colour?

Yes of course!

I will modify the workbook and upload a new version asap. This time hopefully illustrating what my intentions are

Thank you for taking your time with this.

Avoid useing merged cells.
I deleted the first row and then applied:

Sub snb()
  sq = Sheets(1).Cells(1, 1).CurrentRegion.Resize(, 9)
    
  For j = 2 To UBound(sq)
    Cells(j, 1).Resize(, 9).Interior.ColorIndex = IIf(sq(j, 3) = sq(j, 8), 12, 14)
  Next
End Sub

perhaps I have not understood completely.
the file is returned with a macro "test" in module 2. after running the macro the sheet "ark1" will look like this . Is this what you want?

the macro is also copied here

Sub test()
Dim j As Integer, k As Integer
j = Cells(Rows.Count, "A").End(xlUp).Row
'MsgBox j
ActiveSheet.Cells.Interior.ColorIndex = xlNone
For k = 3 To j 'all the rows of data excluding column headings
If Cells(k, "B") = Cells(k, "G") And Cells(k, "C") = Cells(k, "h") Then
Cells(k, "A").EntireRow.Interior.ColorIndex = 3
End If
If Cells(k, "B") = Cells(k, "G") And Cells(k, "C") <> Cells(k, "h") Then
Cells(k, "A").EntireRow.Interior.ColorIndex = 6
End If
Next k


End Sub

Probably me being dense. Everyone else seems to have understood.

Hehe no worries, description was a bit vague

So new workbook is uploaded, this time with a text box describing what I would like to do and the cells colored accordingly.

Don't think I mentioned that even though both data sets are sorted it is possible for entries to occur in one set but not in the other. So a line by line comparison is not possible.

I have added this situation to the example sheet as well.
Best regards

Have you tried the two solutions already offered?

Sorry, missed the part with the merged cells. The last bit of code does the trick. Keep It Simple Stupid works

Thanks for helping out.