Compare 2 sets of data and highlight the difference value

[Abinav]

Hello

Please find my requirements. I have 2 sets of data which needs to be sorted to ascending order. Headers starts from Row 4. 1st data is with headers 'Account(with numbers- Col A and 'Total-Col B (with positive & negative values also ). 2nd data is with the same headers and values-Col D & E. I want to compare the column A & D first vice versa and i want the code to display the missing values in both the columns. then i want the code to compare the total columns B & E and display the amount does not match. Also I need the code to convert the values to number since the Account Column values are in text.

Grand total fields needs to be ignored.
Note- Col B has few values in negative values which is there in Col E. Same value as below . code should ignore these values regardless of the negative figures since the values are same. For example,
Col B Col E
53024 -132 53024 132
53025 -7327.44 53025 7327.44

Thanks for your help

 

[Abinav]

Thanks Mart.

Code to remove minus is working well
This code is comparing and highlighting values in Column B & E. I do not want to compare those columns. Please can you alter the code.I have sent the image. Can you view?

 

[mart37]

lastRow = Report.UsedRange.Rows.Count - 1
For i = 2 To lastRow
    For j = 2 To lastRow
        If Report.Cells(i, 1).Value <> "" And Report.Cells(j, 4).Value <> "" Then
            If InStr(1, Report.Cells(i, 1).Value, Report.Cells(j, 4).Value, vbTextCompare) > 0 Then
                Report.Cells(i, 1).Interior.Color = RGB(255, 255, 255) 'White background
                Report.Cells(i, 1).Font.Color = RGB(0, 0, 0) 'Black font color
                j = lastRow
            Else
                Report.Cells(i, 1).Interior.Color = RGB(156, 0, 6) 'Dark red background
                Report.Cells(i, 1).Font.Color = RGB(255, 199, 206) 'Light red font color
            End If
         End If
    Next j
Next i
For i = 2 To lastRow
    For j = 2 To lastRow
        If Report.Cells(i, 4).Value <> "" And Report.Cells(j, 1).Value <> "" Then
            If InStr(1, Report.Cells(i, 4).Value, Report.Cells(j, 1).Value, vbTextCompare) > 0 Then
                Report.Cells(i, 4).Interior.Color = RGB(255, 255, 255) 'White background
                Report.Cells(i, 4).Font.Color = RGB(0, 0, 0) 'Black font color
                j = lastRow
            Else
                Report.Cells(i, 4).Interior.Color = RGB(156, 0, 6) 'Dark red background
                Report.Cells(i, 4).Font.Color = RGB(255, 199, 206) 'Light red font color
            End If
         End If
    Next j
Next i

 

[Abinav]

Thanks Mart. It is working well. Can you tell me the code to compare the data values from Range Row 5. Currently it is comparing the header also which is starting from Row 3.

Otherwise code looks great

 

[mart37]

5 To lastRow

 

[Abinav]

Thanks Mart. It is works well now.. Got the desired output..

Thanks a lot for your help

 

[Abinav]

Hi Mart
I am trying to copy the contents of the sheet 1 to sheet 2. Used the below code. My code copies the column Document currency at the last. after this i want to go to the first cell of the sheet 1 and then go to the output sheet 2. Please can you help

Sub Documentcurrency()
Dim sh As Worksheet, fn As Range
Set sh = Sheets("Sheet1")
Set fn = sh.Rows(2).Find("Document currency", , xlValues, xlWhole)
If Not fn Is Nothing Then
fn.Offset(1).Resize(sh.Cells(Rows.Count, fn.Column).End(xlUp).Row, 1).Copy Sheets("Upload").Range("M3")
Else
MsgBox "Search Item Not Found!"
Exit Sub

End If
Nextsheet
End Sub
--------------------------
Need a code here to go to cell A2 of my sheet 1 and then it has to take me to the output sheet 2. can you help?
-----------------------
Sub Nextsheet()
ActiveSheet.Next.Activate


End Sub

 

[Abinav]

Hi Mart

Can you assist me?

When i run the macro after copying the contents to the output sheet. the range in the first sheet 1 showing in Column X. I want the range to be in Cell A2

 

[mart37]

do you mean: sh.Range("A2").Select

 

[Abinav]

Yes but i dont know where to add that code. Once the macro is executed it is navigating me to the column X. But i want it to be in Range A2.

 

[mart37]

I don't know the structure of your macro's.
Subroutine Documentcurrency() is called from an other subroutine. Which subroutine?
What is the meaning of subroutine Nextsheet()?