Consulting

Results 1 to 11 of 11

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

  1. #1
    VBAX Regular
    Joined
    Oct 2013
    Posts
    74
    Location

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

    Hi,
    Kindly note that this post is posted at https://www.excelforum.com/excel-pro...ed-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.

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Can you repost your attachment to show a desired result?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Oct 2013
    Posts
    74
    Location
    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

  5. #5
    VBAX Regular
    Joined
    Oct 2013
    Posts
    74
    Location
    Quote Originally Posted by mdmackillop View Post
    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.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Regular
    Joined
    Oct 2013
    Posts
    74
    Location
    Thanx a lot and hope your help will ease my work in original large data too.

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    VBAX Regular
    Joined
    Oct 2013
    Posts
    74
    Location
    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.

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    VBAX Regular
    Joined
    Oct 2013
    Posts
    74
    Location
    Thanx it worked well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •