Conditional formatting overwritten with copy

  • Thread starter Thread starter BillGriz
  • Start date Start date
B

BillGriz

Excel 2003. I have a worksheet using conditional formatting to put shading in
cells based on what's selected in another cell.
The cells with conditional formatting are unloacked to allow data entry. If
data is copy and pasted from another cell it overwrites the conditional
formatting. It only stays intact if I use the "Copy value" selection. The
problem is I won't be the only one using the file.
So the question is, how do I protect the conditional formatting or limt the
copy/paste function to values only?
Thanks
 
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.

Copy cells and paste into the CF cells.


Gord Dibben MS Excel MVP
 
Good Stuff!!!
Thank you!
Bill

Gord Dibben said:
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.

Copy cells and paste into the CF cells.


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top