Error when using colorindex in a macro: "Runtime error 1004"

Question

| | I've written a macro to highlight cells that match a certain criteria. Another to rest the highlighting. Tested these at work and put a password on the file to protect. At first had issues with the rest macro - error on colorindex line. This appeared to<br> resolve itself when i reset password, saved file, re-opened etc. no change to code. Emailed file to myself to check at home and when launched, file asked for password and fell into runtime error 1004. |<br>| --- | || --- || | If tgt > grd(times) Then ActiveCell.Offset(0, move_over).Select Marker = 2 With Selection .Interior.ColorIndex = 13 - this is highlighted as error code End With End If |<br>| --- | || --- || | Same thing is happening with the reset macro. <br><br>Here it is in it's entirety.<br><br><br>Sub Reset_all() Range("$AG$3", "$BF$40").Select With Selection .Interior.ColorIndex = xlNone End With Range("$B$3").Activate End Sub |<br>| --- | || --- |

Answer 1

You can protect certain cells by format-cell-protection. they are set as protected (locked) by default. You can unlock individual cells. When the sheet is protected only the locked cells can not be changed. HOWEVER, certain formatting functions (like setting color) can not be performed on a protected sheet regardless of the cell's protection. The very best way to do this is to have the macro unprotect the sheet, do its stuff, and then protect it again.

Answer 2

What are you password protecting? If the sheet is protected you can not set or reset the color.

Answer 3

Thanks for this.  Can I set a protection to enable me to protect certain cells.  I cannot have colleagues trying to fill in data in the wrong columns.  Can I set and reset the protection from the macro?  I'm a self taught novice dealing with "real" novices.