Consulting

Results 1 to 7 of 7

Thread: VBA to replace previous table and check if one cells value is equal to the previous o

  1. #1
    VBAX Regular
    Joined
    Dec 2019
    Posts
    11
    Location

    VBA to replace previous table and check if one cells value is equal to the previous o

    Hello,

    I couldn't figure it out on my own so i decided to turn to the professionals.

    I have a Macro in which if a value is entered in a cell right of the table, the table values ,formulas,format from above is copied right below the old one. Now the thing i need is that one of the copied cells would check if it is the same value from the previous table and create pop up: New value (or at least change it's colour). To put it simply i attached a workbook and the whole code. IF a value is entered in G column. The previous cells are copied and pasted nearby. What is need is when a new table is created, to check if (For this instance) B24=B35 and if not create a Message box or at least change it's colour. Thank you for any tips or help.

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r As Range
        
        Set r = Target.Cells(1, 1)
        
        If Len(r.Value) = 0 Then Exit Sub
        
        If r.Column <> 7 Then Exit Sub
        If r.Row Mod 11 <> 1 Then Exit Sub
    
        r.Offset(-11, -6).Resize(11, 5).Copy r.Offset(0, -6)
     End Sub

    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    The previous cells are copied and pasted nearby. What is need is when a new table is created, to check if (For this instance) B24=B35 and if not create a Message box or at least change it's colour. Thank you for any tips or help.
    If the block of cells is copied, why would the NOT be the same (at that point)
    ---------------------------------------------------------------------------------------------------------------------

    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

  3. #3
    VBAX Regular
    Joined
    Dec 2019
    Posts
    11
    Location
    Because later another value will be entered in adjacent cell which almost always will be different. The copied block also copies formulas. The main cell with the formula looks that the entered value in an adjacent cell and brings a unique value, which may be different in another block of cells.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    In the WS code module

    In the test attachment WB, I put some formulas in to the copy area that point to col M to make sure that changes were picked up

    Conditional Formatting compares the 'copied' to the 'copy' including cells with formulas


    Option Explicit
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r As Range, rSrc As Range, rDest As Range
        Dim sDest As String
        
        Set r = Target.Cells(1, 1)
        
        If Len(r.Value) = 0 Then Exit Sub
        
        If r.Column <> 7 Then Exit Sub
        If r.Row Mod 11 <> 1 Then Exit Sub
    
    
        Set rSrc = r.Offset(-11, -6).Resize(11, 5)
        Set rDest = r.Offset(0, -6).Resize(11, 5)
        sDest = rDest.Cells(1, 1).Address(False, False)
    
    
        rSrc.Copy rDest
        
        With rDest
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & sDest & "<>OFFSET(" & sDest & ",-11,0)"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = vbRed
            .FormatConditions(1).StopIfTrue = False
        End With
    
    
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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

  5. #5
    VBAX Regular
    Joined
    Dec 2019
    Posts
    11
    Location
    Tested the attachment. Run time error 5. Invalid procedure call or argument in
       .FormatConditions.Add Type:=xlExpression, Formula1:="=" & sDest & "<>OFFSET(" & sDest & ",-11,0)"

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Hmmm -- works for me

    Capture.JPG

    What version of Excel are you using?

    Put a breakpoint on that line and go to the Immediate Window and see what

    "=" & sDest & "<>OFFSET(" & sDest & ",-11,0)"

    is
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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
    Dec 2019
    Posts
    11
    Location
    Figured it out. Thank you for your work and time that you putted in.

Posting Permissions

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