VBA code doesn't run when sheet is protected

Question

I'm using VBA code that will make some cells background flash as given here:

http://www.mrexcel.com/forum/showthread.php?t=32791

The cells effected by the VBA are unlocked, even still, whenever the sheet is protected, I get a VBA run-time error about the color change.

How do I fix this?

Answer 1

Two options:

You can allow to format cells, when you protect the worksheet, on you can let the macro unprotect the worksheet before it change the color, and protect it before End Sub.

ActiveSheet.UnProtect Password:="JustMe"

Other code

ActiveSheet.Protect Password:="JustMe"

I guess that the second solution is best, if you want to prevent the user from formatting the unlocked cells.

Answer 2

I don't want to unlock the sheet, but I did figure out this which is probably what your first suggestion was.  It doesn't matter what the password is, but it keeps the sheets locked:

Worksheets("Sheet1").Protect AllowFormattingCells:=True

Worksheets("Sheet2").Protect AllowFormattingCells:=True

'Flashing cell code

Worksheets("Sheet1").Protect AllowFormattingCells:=False

Worksheets("Sheet2").Protect AllowFormattingCells:=False

This code does mostly what I need; however, can I have only a selected range of cells from multiple sheets affected?  ie. Can I have A1:C1 on "Sheet1" and "A1:Z1" on "Sheet2" affected by this code and no other cells?