Cut Cell Data But Don't Remove Fill Color?

[miketurn]

Hello Everyone,

I am currently using Excel 2000 and I was wondering if someone could help me out, I would imagine this has been fixed in newer versions but I am not sure.

Say you have Column A the first three rows have a fill color of RED
The rest of Column A has a fill color of YELLOW
Rows 10, 11 and 12 have text in them (with YELLOW filled background)
If you select 10, 11 and 12 and go to cut
Then you paste them in Column A in Rows 1, 2 and 3

Rows 1, 2 and 3 will have the data and the YELLOW fill pasted in them (which is great)
but left behind is rows 10, 11 and 12 and they now are no longer YELLOW, they are now WHITE

The fill color gets removed completely, where I wanted them to stay YELLOW as well..


Is there a simple way to prevent this from happening?

Would imagine a macro could fix this, but it seems like it would be overkill for something that I hope has a simpler solution.

If anyone has any advice, please share.
Thank You to anyone who reads this

(Sorry to hear about the attacks on this site, seems odd that so many forums seem to be having problems like this, what would be the point of doing something like that, to an Excel Forum?)

 

[Joe4]

Instead of using Cut, use Copy. Then the value and formatting of the original cell remain intact.
If you want to remove the value from the original cell, then simply delete it (clear contents) after the Copy.

 

[miketurn]

Thank You for your response, that is the current method I am jusing but I find it very unefficient and at times confusing to have to keep going back and deleting the doubled content.
I was hoping there was a better way, if anyone else has any information to share please let me know.
I would willing to give a macro a try if that is my only option to solve this, does anyone have anything like this that they have already created for themself?

Thanks Again

 

[miketurn]

Seems odd that this problem still exists, I remember looking this up a long time ago.
I have seen others complain about this also, but never found any solutions other than the method mentioned above.
Is everyone just using the COPY and manually delete method?
There has to be something better than this, there just has to be.

 

[Joe4]

It isn't a bug, that is the way it has always been (and it makes sense logically), and the workaround is rather easy/simple.
"Cut" was created to MOVE cells, so you take/move EVERYTHING with you, including the format.
"Copy" leaves the original data intact, and makes a copy in the new cell.

This can also be done very easily with a macro, and you can get the VBA code needed for this very easily too. Just turn on the macro recorder and perform the two steps I mentioned in my first reply.
If you do that, you will have the VBA code you need to do that.

 

[miketurn]

@Joe4
No, I understand that it isn't really a "bug" but it seems odd that there isn't an option somewhere to turn this on or off, you know, to fulfill my specific needs

I tried quite a few methods of recording the macro as you mentioned but I unfortunately was unsuccessful in producing a macro to achieve what I am looking for.

The way I would look at it is it would have to be a three step process.
1.) "Copy" the cell selected
2.) Select the new cell you want to copy the data to
3.) Activate Macro that will "Paste" the copied data into that cell, yet also know what cell the data was copied from and "Delete" that cell.

The problem I had was obviously Step 3.
You have to "Copy" the cells information from Step 1 in order to create Step 3, but when you try this it unselects the cell, and also once you select the cell to copy to in Step 2, the original cell will lose focus.
I am probably missing something very simple, but is this really a task that I can perform by "recording" a macro? or are more macro skills needed?

When you see all this that is needed just to accomplish a relatively simple task, it just seems odd that this was not thought about. Remember you (the user) also now has to setup a tool to activate this macro and also then loses the ability to "Undo" this action if needed.

Anyways, if anyone has any ideas of how to achieve this please let me know.

 

[miketurn]

I am also looking for a macro to do the same process as above, but instead of clearing out the cells contents, I want it to delete the row entirely.

I would imagine if someone provides a macro, it will contain

Selection.ClearContents

and I would replace that line of code with the following....

Selection.EntireRow.delete

Hopefully

 

[Michael M]

I'm getting a bit confused...earlier you stated

Rows 1, 2 and 3 will have the data and the YELLOW fill pasted in them (which is great)
but left behind is rows 10, 11 and 12 and they now are no longer YELLOW, they are now WHITE

Now you are saying you want rows 10,11 & 12 deleted entirely after the data is copied to rows 1,2 & 3 ???

but instead of clearing out the cells contents, I want it to delete the row entirely.

 

[miketurn]

@Michael M
In the beginning of my post #7 I state that "I am also looking for...."

So I am looking for....
A macro that will allow me to CUT cell data and background color and after it is pasted elsewhere, also leave the background color behind in the original cells.

I am also looking for....
The same tool as above, but edited so that when the cells are CUT and pasted elsewhere, those original cells get removed completely.


As I mention in above responses I don't see a way to achieve this by using the macro record tool.
If anyone has any ideas or needs further explanation, please let me know.
Thank You

 

[Michael M]

for this question

So I am looking for....
A macro that will allow me to CUT cell data and background color and after it is pasted elsewhere, also leave the background color behind in the original cells

use

Sub MM1()
Rows("10:12").Copy Range("A1")
Rows("10:12").ClearContents
End Sub

for the 2nd

The same tool as above, but edited so that when the cells are CUT and pasted elsewhere, those original cells get removed completely.

Sub MM2()
Rows("10:12").Copy Range("A1")
Rows("10:12").delete
End Sub