Color Rows after ComparingClosed

Question

"Hello world",



I have an excel file with three sheets. What I've done so far is to compare sheet1 with sheet2.
Every new or updated record that sheet1 has, and sheet2 does not, makes my macro (vba) copy the whole row and then paste it one line under the next into sheet3. 
So far it is working.
Now i like to have a macro that checks if the record in sheet3 is a new one or if it just updated.
The solution should be displayed by colouring the certain line in sheet3.

So far I got:


MaxCol = tab1.Range("A1").SpecialCells(xlCellTypeLastCell).Column
If tab2.Range("A1").SpecialCells(xlCellTypeLastCell).Column > tab1.Range("A1").SpecialCells(xlCellTypeLastCell).Column Then
    MaxCol = tab2.Range("A1").SpecialCells(xlCellTypeLastCell).Column
End If
 
MaxRow = tab1.Range("A1").SpecialCells(xlCellTypeLastCell).Row
If tab2.Range("A1").SpecialCells(xlCellTypeLastCell).Row > tab1.Range("A1").SpecialCells(xlCellTypeLastCell).Row Then
    MaxRow = tab2.Range("A1").SpecialCells(xlCellTypeLastCell).Row
End If
                 
iRow = 1
iCol = 1
                
    For iCol = 1 To MaxCol
        For iRow = 1 To MaxRow
  
var2 = Application.Match(tab3.Cells(iRow, iCol), tab2.Columns(iCol), 0)
        If var2 Then
            
            tab3.Cells(iRow, Columns.Count).Interior.ColorIndex = 4
        Else
            var2 = Application.Match(tab3.Cells(iRow, iCol), tab2.Columns(iCol), 0)
            If var2 Then
                    tab3.Cells(iRow, 2).Interior.ColorIndex = 6
                    tab3.Cells(iRow, 1).Interior.ColorIndex = 6
                Else: On Error Resume Next
            End If
        End If

Next iRow
Next iCol



-------------------------------------------------------------------------------------------



tab3 = sheet3
tab2 = sheet2 (the old data)


Out of some reason it does not work. It does not color anything.


Thaks a lot for your help!!!


sr7

venkat1926 · Answer

one way is while copying, the entry which is in sheet 1 and not in sheet 2, then itself color the entry.

as You have already copied many entries now this cannot be done 
so the logic should be 

any entry in sheet 3 is available in sheet 1 and not sheet 2 it should be colored. 

the macro will be SOMETHING LIKE THIS. NEEDS DEBUGGING AND MODIFICATION IF NECESSARY
I have only shown the model. check and post feedback

Sub test()
Dim rng As Range, c As Range, cfind1 As Range, cfind2 As Range
On Error Resume Next
With Worksheets("sheet3")
Set rng = .usesdrange
For Each c In rng
With Worksheets("sheet1")
Set cfind1 = .Cells.Find(what:=c.Value, lookat:=xlWhole)
If cfind1 Is Nothing Then GoTo line1
With Worksheets("sheet2")
Set cfind2 = .Cells.Find(what:=c.Value, lookat:=xlWhole)
If cfind2 Is Nothing Then GoTo line2
End With
End With
line2:
c.Interior.ColorIndex = 3
line1:
Next c
End With

End Sub

sr7 · Answer

hi and thanks!
it works just in parts.

the script cannot differentiate between var4 which is in the column "B"of "tab2" and which is not.
these which are present in tab2 are supposed to be colored yellow (because they are only an update) and these which are new should be green...



cfind1 As Range, cfind2 As Range
    
    For iCol = 1 To MaxCol
    For iRow = 1 To MaxRow
        If tab3.Cells(iRow, iCol).Value = 0 Then
            On Error Resume Next
            Else
            var4 = tab3.Cells(iRow, iCol)      
            With tab2.Range("B:B")
             Set cfind1 = Cells.Find(What:=tab3.Cells(iRow, iCol), LookIn:=xlValues)
            If cfind1 Then
            tab3.Cells(iRow, 2).Interior.ColorIndex = 4
            Else
            tab3.Cells(iRow, 2).Interior.ColorIndex = 6
            End If
            End With
         End If
        
    Next iRow
    Next iCol





Thanks a lot for the effort...

sr7

Color Rows after Comparing

2 responses

Similar discussions

Subscribe To Our Newsletter!