Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting overwritten with copy
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting overwritten with copy
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 On Thu, 22 May 2008 10:19:12 -0700, BillGriz wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting overwritten with copy
Good Stuff!!!
Thank you! Bill "Gord Dibben" wrote: 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 On Thu, 22 May 2008 10:19:12 -0700, BillGriz wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting overwritten with copy
Thanks for the feedback.
Gord On Thu, 22 May 2008 12:21:05 -0700, BillGriz wrote: Good Stuff!!! Thank you! Bill "Gord Dibben" wrote: 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 On Thu, 22 May 2008 10:19:12 -0700, BillGriz wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy conditional formatting | Excel Discussion (Misc queries) | |||
... Can I Copy Conditional Formatting... | Excel Discussion (Misc queries) | |||
Copy Conditional Formatting | Excel Discussion (Misc queries) | |||
copy conditional formatting | New Users to Excel | |||
How to copy conditional formatting | Excel Worksheet Functions |