Results 1 to 10 of 10

Thread: Copy only cells that have data from one row to another, highlighting changes
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2017
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Copy only cells that have data from one row to another, highlighting changes

    Hello, I am trying to write a macro to copy information from a range (E22:DQ22) to another range directly above it (E8:DQ8), but only if the cells have values, and highlighting the changes. For example, if there is already information in cell E8 and F8, and cell E22 has data while F22 does not, the value in E8 will be replaced by that in E22, while the data in F8 will remain the same. Cell E8 will now be highlighted, while F8 will not.

    I imagine I need some type of loop, but I'm not sure the bets way to do it. Any ideas?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,109
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Copy only cells that have data from one row to another, highlighting changes

    Does E22:DQ22 contain formulae, or hard values?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jul 2017
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy only cells that have data from one row to another, highlighting changes

    Quote Originally Posted by Fluff View Post
    Does E22:DQ22 contain formulae, or hard values?
    Just values.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,109
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Copy only cells that have data from one row to another, highlighting changes

    In that case, how about
    Code:
    Sub uncleBajubjubs()
       Dim Rng As Range
       For Each Rng In Range("E22:DQ22").SpecialCells(xlConstants)
          With Rng.Offset(-14)
             .Value = Rng.Value
             .Interior.Color = 45678
          End With
       Next Rng
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Jul 2017
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy only cells that have data from one row to another, highlighting changes

    Quote Originally Posted by Fluff View Post
    In that case, how about
    Code:
    Sub uncleBajubjubs()
       Dim Rng As Range
       For Each Rng In Range("E22:DQ22").SpecialCells(xlConstants)
          With Rng.Offset(-14)
             .Value = Rng.Value
             .Interior.Color = 45678
          End With
       Next Rng
    End Sub
    That worked perfectly, thank you!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,109
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Copy only cells that have data from one row to another, highlighting changes

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Jul 2017
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy only cells that have data from one row to another, highlighting changes

    So I've been using this macro and it's been working fine, however I wanted to add a small update to make it better. It's possible that the user may wish to put the data not just 14 rows above, but also 13,12,10,9,8,6,5 or 4 rows above, and I want to have a way to let the user choose which row they'd like. Do you think a message box would be the best way to do this?

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,109
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Copy only cells that have data from one row to another, highlighting changes

    How about
    Code:
    Sub uncleBajubjubs()
       Dim Rng As Range
       Dim Offst As Variant
       Offst = InputBox("please enter the number of rows to offset")
       If Offst = "" Then Exit Sub
       For Each Rng In Range("E22:DQ22").SpecialCells(xlConstants)
          With Rng.Offset(-Offst)
             .Value = Rng.Value
             .Interior.Color = 45678
          End With
       Next Rng
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Jul 2017
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy only cells that have data from one row to another, highlighting changes

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub uncleBajubjubs()
       Dim Rng As Range
       Dim Offst As Variant
       Offst = InputBox("please enter the number of rows to offset")
       If Offst = "" Then Exit Sub
       For Each Rng In Range("E22:DQ22").SpecialCells(xlConstants)
          With Rng.Offset(-Offst)
             .Value = Rng.Value
             .Interior.Color = 45678
          End With
       Next Rng
    End Sub
    Perfect again, thanks!

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,109
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Copy only cells that have data from one row to another, highlighting changes

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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