I'm trying to compare cells in 2 tabs (master and test) and if there are changes in test then color changes in any kind of color and copy & paste it to master file.
UPDATED:
Here is required code
Sub test()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Dim jRow As Long
Dim jCol As Long
strRangeToCheck = "A1:V1000"
' If you know the data will only be in a smaller range, reduce the size of the ranges above.
Debug.Print Now
varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
Debug.Print Now
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetB(iRow, iCol) = varSheetA(iRow, iCol) Then
' Cells are identical.
' Do nothing.
Else
Sheets("Sheet1").Select
Cells(iRow, iCol).Interior.ColorIndex = 44
Sheets("Sheet2").Select
Cells(iRow, iCol).Interior.ColorIndex = 44
Sheets("Sheet2").Select
Cells(iRow, iCol).Copy
Sheets("Sheet1").Select
Cells(iRow, iCol).PasteSpecial xlValues
Cells(iRow, iCol).PasteSpecial xlFormats
' Cells are different.
' Code goes here for whatever it is you want to do.
End If
Next iCol
Next iRow
MsgBox ("Done")
End Sub
Find the last used cell in one of the worksheets.
dim lr as long, lc as long
lr= application.max(dWS.cells.specialcells(xlCellTypeLastCell).row, _
mWS.cells.specialcells(xlCellTypeLastCell).row)
lc= application.max(dWS.cells.specialcells(xlCellTypeLastCell).Column, _
mWS.cells.specialcells(xlCellTypeLastCell).Column)
For Each c In dWS.Range("A2", dWS.cells(lr, lc))
If Not dWS.Cells(c.Row, c.Column).Value = mWS.Cells(c.Row, c.Column).Value Then
dWS.Cells(c.Row, c.Column).Interior.Color = vbYellow
End If
Next