Undo

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
Hello to All
With this code in sheet1 I select a cell , fill it in yellow, copy the entire row and past it in the first empty row in sheet2
Code:
Sub tranferdata()
With Selection.Interior
        .ColorIndex = 6
    With Selection.EntireRow.Copy
    Sheets("Sheet2").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    End With
    End With
End Sub

What I need is if I choose to undo a "transfer" by doing this:
In sheet1 I click a "yellowed cell"
Cell turns to "no fill"
find matched cell in sheet2
When found does an EntireRow.delete .
Returns to sheet1

Thank you
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
I beleive this will work for you:

Code:
Sub untransferdata()
If Selection.Interior.ColorIndex = 6 Then
 valuetofind = Selection.Value
 Incrementor = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1
 ValueFound = True
 Do
   Incrementor = Incrementor - 1
   If Incrementor = 0 Then
     MsgBox ("Error.  Value not found")
     ValueFound = False
     Exit Do
   End If
 Loop Until Sheets("Sheet2").Cells(Incrementor, 1).Value = valuetofind
 If ValueFound = True Then
   Sheets("Sheet2").Cells(Incrementor, 1).EntireRow.Delete
    Selection.Interior.ColorIndex = xlNone
 End If
End If
End Sub

Take care

Owen
 
Upvote 0

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
Owen great work with many thanks.
Hope you fly as well as you write code.
Best regards
Nick
 
Upvote 0

Forum statistics

Threads
1,191,219
Messages
5,985,335
Members
439,958
Latest member
qb0000

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
Top