Protect Conditional Formatting on Unlocked Cells

Hi,

I have a protected worksheet. Some cells in the sheet are "Unlocked" because I want to change values. I also have conditonal formatting attached to these Unlocked cells. If someone Copy & Paste within the range of unlocked cells, my conditional formatting is destroyed. Is there a way to protect conditional formatting on unlocked cells within a protected sheet?

Are you happy to use VBA?
- it is the only way

Thanks,
I use VBA quite much. I was hoping for a built in solution. I believe many users have the same issue.

It is the same problem with DataValidation - copy/paste destroys that too
Microsoft should have addressed this years ago

There is more than one way to attack your problem - do you want to prevent users copy/pasting if cell has CF or do you want users to be able to do that?

My objective is that users only can Paste Values in unlocked cells on a protected sheet. Nothing else.

Test in attached workbook - is it too restrictive?
- cut,copy & paste all disabled

The only way to copy and paste values:
SELECT a range \ {CTRL} k \ select pasteTo cell with mouse \ click "OK"

I have not tested it to any extent
- I suspect it needs refining to deal with user "finger trouble" when used in conjunction with replacement macro

But do you think this is something you can use?


Replacement macro

Please Login or Register  to view this content.

Credit for this code due to Ken Puls
Found here http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

Please Login or Register  to view this content.

Thanks for your reply,
I'll look into this material. I believe I can find something useful. At least I will leatn some new VBA code.

I will post another option on Sunday

How many different conditional formatting conditions are there in the "unlocked" cells?

Hi,

(This request should probably be moved to VBA discussion group now. My first intention was to find a bulit in solution in Excel application)

I am very close finding a solution that suits me. Following code prevents a UserMode = "User" to use Paste or Autofill. I wanted to allow only "Paste Special Values". However:

Please Login or Register  to view this content.

returned "Paste Special" for all type of Paste Special Operaration. I wanted to prevent "Paste Special Formats" also. Now I can't differ between paste Values or Formats.

Please Login or Register  to view this content.

This is better than nothing.

Thread moved to VBA section.

How about this
- the values are pasted for ordinary pasting

Please Login or Register  to view this content.

It may be possible to paste Fill values only too. The basic code is

Please Login or Register  to view this content.

- I will investigate further....

This seems to work with "Fill"
- user asked to select the fill range again
- on error required in case user messes up range selection

Please Login or Register  to view this content.

Kev_,

This solution is great.

I modified some for Auto Fill so you don't have to use InputBox. It works for me. Maybe I haven't thought of everything that can occur.

Please Login or Register  to view this content.

If it works then that is better for user

I would like understand why it gives correct result
It looks wrong because
source = selection
destination = selection
But if Excel fills correctly why worry?

Thanks Kev,

I judge this request as solved. Nice support from you.

I found that AutoFill only worked in direction Down or Right before. Now it works for Left and Up also.

Please Login or Register  to view this content.

See post#16

Please Login or Register  to view this content.

gives the correct result ONLY if single cell is used to fill down

BUT if
A1 = 2 and A2 = 4

Normal Fill returms
A3= 6, A4 = 8 etc

Above code returns
A2 = 2, A3 = 2 , A4 = 2

I will investigate....

Following code seems to solve the issue with 2 adjacent cells to be Auto Filled:

Please Login or Register  to view this content.

Or even better:

Please Login or Register  to view this content.

better ?
yes and no

yes - FILL works perfectly

BUT code in post#19, pastes values AND format
- which takes you back to your original problem "my conditional formatting is destroyed"

I have tried both Paste & Autofill. It works fine for me. I don't paste the format.

I was referring to Fill in both
VALUES = GOOD, FORMAT = NOT GOOD

Here are BEFORE and AFTER images
- where font colour is RED before and changed to black by the fill

Fill-BEFORE.jpg Fill-AFTER.jpg

We don't get the same result. On my computer it seems to work. Strange.

VALUES
FORMAT

Capture.PNG

I deleted the old file and created a new workbook and pasted only that code in there and it worked correctly
Good

Nice kev_,

I'll just show the last code if someone else is interested.

Please Login or Register  to view this content.