Help Customising a macro

[Patrick]

I found the macro below, I have customised it a bit my self but now I would like to make it fill the entire row gray on both Revised and Original before marking the different cell in yellow.

Thank you,

Sub RunCompare()
Call compareSheets("Revised", "Original")
End Sub

Sub compareSheets(shtRevised As String, shtOriginal As String)
Dim mycell As Range
Dim mydiffs As Integer
'For each cell in Revised that is not the same in Original, color it yellow
For Each mycell In ActiveWorkbook.Worksheets(shtRevised).UsedRange
If Not mycell.Value = ActiveWorkbook.Worksheets(shtOriginal).Cells(mycell.Row, mycell.Column).Value Then

mycell.Interior.Color = vbYellow
mydiffs = mydiffs + 1

End If
Next
'Display a message box to demonstrate the differences
MsgBox mydiffs & " differences found", vbInformation
ActiveWorkbook.Sheets(shtRevised).Select
End Sub

 

[Smallman]

Hi Patrick

Would you consider using Conditional formatting? This is what CF was designed to do in XL. It operates the same way as your macro but without the msg at the end of the procedure.
File attached to show workings.

Take care

Smallman

 

[Patrick]

Hi Patrick

Would you consider using Conditional formatting? This is what CF was designed to do in XL. It operates the same way as your macro but without the msg at the end of the procedure.
File attached to show workings.

Take care

Smallman

Hi Smallman,

Thanks for the advice, I have tried a similar way using conditional formatting however I fount it to be annoying whenever I would move cells and insert rows (I am trying to make the Revised and Original data match) It changes the range of cells for CF. Is there any way to lock CF to a table? I have tried typing the table name in the range same problem.

Regards

Patrick