protect cell formatting

[David]

Excel 2003 - I have a spreadsheet that has various cells with different types
of formatting - ie: bold / shade / font size etc. When a user copies / pastes
from text from 1 cell to another, it carries over the formatting as well as
the text - I want just the text to be copied.

 

[מיכ×ל (מיקי) ×בידן]

Instead of "regular" Paste - try 'PasteSpecial' > Values' > 'OK'
Micky

 

[David]

Yes, thank you. I was hoping for someway to "lock" the formatting, as there
are several users of this spreadsheet - some are not so computer savy.

 

[Gord Dibben]

If you are willing to use some VBA try this event code.

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
End Sub

This is sheet code. Right-click on the sheet tab and "View code"

Copy/paste the code into that sheet module.

Alt + q to return to Excel.

When you copy and paste, the destination formatting will be
retained...........source formatting is ignored.

Requires users to enable macros when opening the workbook.


Gord Dibben MS Excel MVP