Move (by dragging) cell value, not formatting.

[miguel3d]

Hi all,

I have a simple sheet and most of the cells are formatted with a bg color. I want to be able to select a cell with a value and move it to another location, but I don't want the formatting to move. The 'protect sheet' angle doesn't work because when you select and move a cell, the formatting moves too.

Here's an example:
1. Start with a new sheet and format cell A1 with a fill color and enter a value in the cell.
2. Now select the cell and move it to B2 by dragging its border.

The bg color moves with the value.

I want to be able to move the cells by dragging them. I don't want to cut-paste special. That would be too tedious considering how often i need to move the values around.

I want to be able to:

1. Click a cell
2. Drag it by it's border to the destination cell. Done, with formatting not moving.

Now, if I move a cell, I can then use the Worksheet_Change function and change the destination cell to the proper formatting. This is easy because immediately after the move the destination cell is still selected. The problem is the source cell. How can I identify what the source cell (or range) was?

Another idea would be to make "Match Destination Formatting" the default. Any way to do that? (As long as the source formatting remains too.)

Any help is greatly appreciated,
miguel3d

 

[Dryver14]

just drag across and when you get the little blue selection box in the bottom right hand corner select fill without formatting

 

[Tom Urtis]

Don't drag while holding down the left mouse button. Instead, drag while holding down the right mouse button. When you reach your destination, let go of the right mouse button. From the pop-up menu, select (left click) the item named "Copy here as values only".

 

[miguel3d]

Thanks for the replies!

While the right-drag idea is slicker (although it's too bad there isn't a "Move here as values only" option), I'd really like to find a solution that allows straight left-dragging with no right-click menus required.

There's a lot of cell moving in this app and a few absolute novices will be using it. Besides, the concept is programmatically simple and I should be able to achieve this.

If I move a cell, I can then use the Worksheet_Change function and change the destination cell to the proper formatting. This is easy because immediately after the move the destination cell is still selected. The problem is the source cell. How can I identify what the source cell (or range) was?

Thanks again for your help,
miguel3d

 

[mrjasongill]

I need help with the same issue. I have a worksheet that people will be editing and I have some conditional formatting set on other cells based on what's in the cells they would be moving. I would prefer to reassign that function in excel when you left click drag to be move values only. Is this possible?

I have a macro to copy the formulas that I have in the other cells now, but I couldn't figure out how to replace the conditional formatting. I guess I could copy and paste special formulas on top of the cells I've moved. Any help would be appreciated.

 

[Schnitzel]

Hi all,

I have a simple sheet and most of the cells are formatted with a bg color. I want to be able to select a cell with a value and move it to another location, but I don't want the formatting to move. The 'protect sheet' angle doesn't work because when you select and move a cell, the formatting moves too.

Here's an example:
1. Start with a new sheet and format cell A1 with a fill color and enter a value in the cell.
2. Now select the cell and move it to B2 by dragging its border.

The bg color moves with the value.

I want to be able to move the cells by dragging them. I don't want to cut-paste special. That would be too tedious considering how often i need to move the values around.

I want to be able to:

1. Click a cell
2. Drag it by it's border to the destination cell. Done, with formatting not moving.

Now, if I move a cell, I can then use the Worksheet_Change function and change the destination cell to the proper formatting. This is easy because immediately after the move the destination cell is still selected. The problem is the source cell. How can I identify what the source cell (or range) was?

Another idea would be to make "Match Destination Formatting" the default. Any way to do that? (As long as the source formatting remains too.)

Any help is greatly appreciated,
miguel3d

Hi
Hope this will help you.
1.)Activate the cell
2.)Highlight the cell contest in the formular bar
3.)Then cut and paste