Script to move a row to other sheet based on cell background color

otamaglimmer

New Member
Joined
Nov 25, 2019
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello there!

I've been working with a table and struggling to find or make a script that would move an entire row from the first sheet of my book (let's call this sheet "NEW") to a second sheet ("OLD") when I change the background color of that row to red. Table's width is A to J and the first row is a header. It would be fine to just compare the last cell color.

It should copy this entire row to the first empty space in that second sheet, and delete the entry from the first one.

Additionally I would love to do the same but to a different sheet when a given cell of that table (cell in column J) is turned blue.

I'm completely new to VBS and I'm getting lost here...
Any kind of help would be much appreciated!

Thanks in advance!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I found a script over here and I changed a few things... Here is its current state:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    
    'If Cell that is edited is in column J and the cell is red
    If Target.Column = 10 And Target.Interior.Color = RGB(255, 0, 0) Then
        'Define last row on completed worksheet to know where to place the row of data
        LrowCompleted = Sheets("OLD").Cells(Rows.Count, "A").End(xlUp).Row
        'Copy and paste data
        Range("A" & Target.Row & ":J" & Target.Row).Copy Sheets("OLD").Range("A" & LrowCompleted + 1)
        'Delete Row from Project List
        Range("A" & Target.Row & ":J" & Target.Row).Delete xlShiftUp
    End If
    
    'If Cell that is edited is in column J and the cell is blue
    If Target.Column = 10 And Target.Interior.Color = RGB(0, 176, 240) Then
        'Define last row on completed worksheet to know where to place the row of data
        LrowCompleted = Sheets("CALLBACK").Cells(Rows.Count, "A").End(xlUp).Row
        'Copy and paste data
        Range("A" & Target.Row & ":J" & Target.Row).Copy Sheets("CALLBACK").Range("A" & LrowCompleted + 1)
        'Delete Row from Project List
        Range("A" & Target.Row & ":J" & Target.Row).Delete xlShiftUp
    End If
    
    Application.EnableEvents = True
End Sub

It ALMOST works as I'd like it to, but I still need to edit every colored cell in column J for the script to kick in. I'd love it to be activated when the cell color changes to RGB(255,0,0) or RGB(0, 176, 240). I make this change manually.

Is there any way to achieve this?

Thanks!!
 
Upvote 0
There is no way to have a script run just because the cell color changes.
you may want to use double click like below:
When you double click on a cell a script will run
Unless you can explain what causes the cell color to change.

You said:
Additionally I would love to do the same but to a different sheet when a given cell of that table (cell in column J) is turned blue.


VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
Upvote 0
Thank you for your answer! I didn't know background color couldn't be used as a trigger...

Answering your question, I do change the row color to red or blue manually when I'm done working on that item.

This is my use case in case it helps:

I have a table with several contacts of people I have to call. Once I've called, one of two things will happen: I will turn the entire row either red or blue. If it's red it should be moved to the "OLD" sheet, and if it's blue to the "CALLBACK" sheet.

My current script will work if I do as follows:
  1. Turn the row red or blue,
  2. Double click on that row's J column cell so it's being edited,
  3. Press Enter or Escape,
  4. The row will be copied over to the appropriate sheet and deleted from the current one.
Using a button (either on the sheet itself or in the ribbon menu) would be nice too, if it can scan all red/blue rows and move them all at once.

I'm not quite sure where to insert that line of code you wrote, but I guess it would replace the first line.
In this case I should make a look checking every written row, right?
 
Upvote 0
You could assign this to a Shape/form control button & run it once you have finished updating the sheet
VBA Code:
Sub otamaglimmer()
    With ActiveSheet
        .Range("A1:J1").AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
        With .AutoFilter.Range.Offset(1)
            .Copy Sheets("Old").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .EntireRow.Delete
        End With
        .Range("A1:J1").AutoFilter 1, RGB(0, 176, 240), xlFilterCellColor
        With .AutoFilter.Range.Offset(1)
            .Copy Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
End Sub
 
Upvote 0
Why not do this:

If you want row copied to sheet named Old double click on column 10
If you want row copied to sheet named Call Back double click on column 9

Would that work?
No need to modify cell colors.
 
Upvote 0
Thank you both for your help!

I ended up creating a button and using Fluff's code, although I'm not sure how it does work, it indeed moves all red and blue rows to their respective sheets.

I'll keep exploring VBA and learning...

Thanks again!
 
Upvote 0
Glad you received a answer you like. Highlighting each row a certain color then running a script to copy the row to another sheet seems like to me a lot of work. But if your happy with this that's all that matters.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top