Comparison of relative data position row-wise & each cellwise of highlighted column

[paradise]

Hi,
Kindly note that this post is posted at https://www.excelforum.com/excel-programming-vba-macros/1190501-comparison-of-relative-data-position-rowwise-and-each-cellwise-of-selected-column.html
Currently I am facing a problem as how to compare which cell/s data has been changed.I have two worksheet namely "Old" & "New".I have kept only few data in these two sheet.Here,I want to highlight the changes in each row cell positions if it is changed in "New" sheet .

I have made some changes in "New" sheet cell position which can be easy seen .Hence this change I want to get highlighted by a macro.

I have a large data more than 30k rows all filled up.How this can be highlighted ?Also a summary if possible which cell/row data has been altered.


I hope this could be resolved as usual

Thanx in advance.

[mdmackillop]

Can you repost your attachment to show a desired result?

[Paul_Hossler]

This assumes that you're comparing old row X to new row X

It won't work if you're trying to compare 'key' values




Option Explicit
Sub HighlightChanges()
Dim rOld As Range, rNew As Range
Dim v As Variant
Dim i As Long

Application.ScreenUpdating = False

Set rOld = Worksheets("Old").Cells(5, 1).CurrentRegion.EntireRow
Set rNew = Worksheets("New").Cells(5, 1).CurrentRegion.EntireRow

For Each v In Array(1, 2, 3, 22, 23, 25)
For i = 1 To Application.Max(rOld.Rows.Count, rNew.Rows.Count)
If rOld.Cells(i, v).Value = rNew.Cells(i, v).Value Then
rNew.Cells(i, v).Interior.Color = vbGreen
Else
rNew.Cells(i, v).Interior.Color = vbRed
End If
Next I
Next

Application.ScreenUpdating = True
End Sub

[paradise]

Thanx a lot for your kind effort.

Your code I think is almost working well with some exception.I have further tested adding some more data in Old as well New worksheet.Following results are there which further required to be resolved:

1.In "Old" sheet,row 11 is added blank row.In "New" sheet,row 9 is added blank row as well.Hence, red highlight is there which is fine and is correct.

But,

2.In "New" sheet,row 12 data Y12 data does not match with "Old" sheet,row 12 data of Y12 which is not highlighted red in "New" sheet

Lastly,

Since,there is no data after row 12 in "Old" sheet,hence the data after row 12 as long as the data is there in "New" sheet must be highlighted red/any colour.

I hope this minor issue would also be resolved.

With Best Rgds,
Suresh

[paradise]

Can you repost your attachment to show a desired result?

I think forum contributor Paul in post 3 has almost done and also attached file with his code in post 4 with few issues which I have clearly stated.I have shown only few specific column whose position of data I did not like to get altered.This is so becoz, in real life I have one master sheet with a lot of calculation and another sheet which comes from other person is being updated daily.Hence,I have to copy additional data which is being updated one in my master sheet.Though there is 90% chances in alteration(addition/deletion/double) in previous data cell position,so to identify this changes, if those cell/rows get highlighted whose alteration is done, then I can do changes easily accordingly in my master sheet and thus ease my work.

Hope this would further clarify the purpose of mine.

[Paul_Hossler]

Your first sample didn't have any blank lines

Since this one does, the macro has to do things a little differently




Option Explicit
Sub HighlightChanges()
Dim wsOld As Worksheet, wsNew As Worksheet
Dim v As Variant
Dim i As Long, rowLastOld As Long, rowLastNew As Long

Application.ScreenUpdating = False

Set wsOld = Worksheets("Old")
Set wsNew = Worksheets("New")

rowLastOld = wsOld.Cells(wsOld.Rows.Count, 1).End(xlUp).Row
rowLastNew = wsNew.Cells(wsNew.Rows.Count, 1).End(xlUp).Row

For Each v In Array(1, 2, 3, 22, 23, 25)

For i = 5 To Application.Max(rowLastOld, rowLastNew)
If wsOld.Cells(i, v).Value = wsNew.Cells(i, v).Value Then
wsNew.Cells(i, v).Interior.Color = vbGreen
Else
wsNew.Cells(i, v).Interior.Color = vbRed
End If
Next i
Next

Application.ScreenUpdating = True
End Sub

[paradise]

Thanx a lot and hope your help will ease my work in original large data too.

[mdmackillop]

Late to the game, but here's a CF solution

Sub Test()
Dim rng As Range
Set rng = Range(Cells(1, 1), ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell))
rng.FormatConditions.Delete
With rng
.FormatConditions.Add xlExpression, , "=A1="""""
With .FormatConditions(1)
.Interior.Color = xlNone
.StopIfTrue = True
End With

.FormatConditions.Add xlExpression, , "=A1=Old!A1"
With .FormatConditions(2)
.Interior.Color = 65280
.StopIfTrue = True
End With

.FormatConditions.Add xlExpression, , "=A1<>Old!A1"
With .FormatConditions(3)
.Interior.Color = 255
.StopIfTrue = True
End With
End With
End Sub

[paradise]

Thanx for the code.

But row 9 is not being highlighted red as row 9 is blank in "New" but in "Old" it is filled with data.Hence,this must be further highlighted.

Additionally,one more thing you can do for me is that you can highlight with 3rd different type of colour for the additional new data which is added in row 13 in "New" sheet since the last data in "Old " sheet is uptill row 12.The others colour which you have done shall be treated as mismatch/alteration of specific position of data uptill row 12.

Mistake - highlighted by Red
Correct- highlighted by Green
New data in New sheet after row 12 or last data of Old sheet - highlighted by Any colour

Hope this can be done additionally.

[mdmackillop]

Sub Test()
Dim rng As Range
Dim Restrict As String
Dim Rw As Long
Set rng = Range(Cells(1, 1), ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell))
Rw = Sheets("Old").Cells(Rows.Count, 1).End(xlUp).Row
rng.FormatConditions.Delete

Restrict = "and(Row()>4,Column()<>24,Column()<26,"

With rng
.FormatConditions.Add xlExpression, , "=" & Restrict & "A1=Old!A1)"
With .FormatConditions(1)
.Interior.Color = 65280
.StopIfTrue = True
End With

.FormatConditions.Add xlExpression, , "=" & Restrict & "Row()>" & Rw & ")"
With .FormatConditions(2)
.Interior.Color = 15000000
.StopIfTrue = True
End With

.FormatConditions.Add xlExpression, , "=" & Restrict & "A1<>Old!A1)"
With .FormatConditions(3)
.Interior.Color = 255
.StopIfTrue = True
End With
End With
End Sub

[paradise]

Thanx it worked well.