ExcelBanter - cell protection

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - cell protection (https://www.excelbanter.com/excel-discussion-misc-queries/190150-cell-protection.html)

i have protected the sheet but still if i copy one cell and paste on another,
the format of the first cell gets replaced by copied cell. how can i avoid
this

Right click on the target cell, select PASTE SPECIAL and select the FORMULA
option if pasting a formula, or the VALUE option if just pasting values.
--
Kevin Backmann

"spandit" wrote:

i have protected the sheet but still if i copy one cell and paste on another,
the format of the first cell gets replaced by copied cell. how can i avoid
this

You could use this event code to retain the Formatting of the target cells.

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

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

Alt + q to return to the Excel window.

Gord Dibben MS Excel MVP

On Thu, 5 Jun 2008 08:04:01 -0700, spandit
wrote:

i have protected the sheet but still if i copy one cell and paste on another,
the format of the first cell gets replaced by copied cell. how can i avoid
this