+ Reply to Thread
Results 1 to 9 of 9

Highlight/Move Entire Row If...

  1. #1
    Registered User
    Join Date
    10-19-2006
    Location
    Nova Scotia
    Posts
    4

    Highlight/Move Entire Row If...

    Hi I am a completely new to VBA and have a question.

    Is there any way to create a formula that will either highlight (in red lets say) an entire row, or move the entire row to another worksheet, if one cell has "Y" in it. Moving the entire row would be preferable.

    I have been able to highlight the one cell, but I need to either highlight the entire row, or move it completely, and this seems to be way beyond my capabilities.

    Any help would be much appreciated.

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    If you are new to VBA, besides standard macros, there are Event macros stored in the sheet modules ...
    Your problem can be solved with
    Private Sub Worksheet_Change(ByVal Target As Range)

    to be more complete, I would need to know which cell (Yes or No) will be the trigger ... and to which worksheet the whole row has to be moved and where ...

    HTH
    Carim

  3. #3
    Registered User
    Join Date
    10-19-2006
    Location
    Nova Scotia
    Posts
    4
    Sorry I didn't leave more info. I feel totally uneducated in all this.

    The "trigger" cell would be H4, and the row would need to be moved to Sheet 2 of the same workbook, in the first available row.

    If this doesn't make sense please let me know. I want to learn more about VBA and macros, but all the information I come across seems a bit complicated (and I am not a dense person, as much as I fell like on right now ).

  4. #4
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Well making tests is the best learning process ...
    In your worksheet, move your mouse cursor to sheet name
    right click and copy following code ...

    Private Sub Worksheet_Change(ByVal Target As Range)

    Set Target = Range("H4")
    If Target Is Nothing Then
    Exit Sub
    Else
    If Target.Value ="Y"
    Target.EntireRow.Cut
    ActiveSheet.Paste Destination:=Worksheets("Sheet2").Range("A1")
    End If
    End If
    End Sub


    Hope this helps
    Cheers
    Carim

  5. #5
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Oops,

    Is it not to A1 in sheet2 that you want to move row to ...
    but to the first available empty row ...

    ActiveSheet.Paste Destination:=Worksheets("Sheet2").Range("A65536").End(xlup).Offset(1,0)


    HTH
    Carim

  6. #6
    Registered User
    Join Date
    10-19-2006
    Location
    Nova Scotia
    Posts
    4
    Thank you very much for your help.

    I have been playing with the code and trying to get it to work the way I need, and I still unable to.

    I think the problem is how I described the issue.

    I need for the row to be moved everytime someone types Y in the H column, not just H4. I also need the empty rows deleted (I think I can find that code here somewhere.

    I have unsuccessfully tried to change the range, but I keep getting debug errors.

    Sorry for being such a newb

  7. #7
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    To have the whole of column H :
    change range line as follows ..

    Set Target = Range("H:H")

    HTH
    Carim

  8. #8
    Registered User
    Join Date
    10-19-2006
    Location
    Nova Scotia
    Posts
    4
    Okay, here I go being a pain in the butt again. I wish I understood what all this means, but I keep getting these darn debuggers!

    This is my code thus far:


    Private Sub Worksheet_Change(ByVal Target As Range)

    Set Target = Range("H:H")
    If Target Is Nothing Then
    Exit Sub
    Else
    If Target.Value ="Y"
    Target.EntireRow.Cut
    ActiveSheet.Paste Destination:=Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)

    End If
    End If
    End Sub


    Now when I enter a Y in my H column the debugger comes up and says:

    Compile Error:
    Syntax Error

    It opens the VBA in debugger and the line "Private Sub Worksheet_Change(ByVal Target As Range)" is highlighted in yellow (I understand that that is telling me the problem lies within that line of code.) So what is wrong with that code?!!

  9. #9
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Sorry for mistake, below is corrected code :

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Intersect(Target, Range("H:H")) Is Nothing Then
    Exit Sub
    Else
    If Target.Value = "Y" Then
    Target.EntireRow.Cut
    ActiveSheet.Paste Destination:=Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
    End If
    End If
    Application.EnableEvents = True
    End Sub

    HTH
    Cheers
    Carim

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1