Previous Topic Next Topic

Color Rows after Comparing

Closed
sr7 - Jul 9, 2009 at 12:26 PM
 sr7 - Jul 10, 2009 at 05:26 AM
"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

2 replies

Answer 1 / 2
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 808
Jul 9, 2009 at 08:16 PM
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
0
Answer 2 / 2
sr7
Jul 10, 2009 at 05:26 AM
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
0