Macro for formatting cells in protected sheets

[Dspace]

Good morning, I have a macro to unprotect a worksheet, hide empty rows & then reprotect the worksheet. I want to allow users to color code (format cells) after his macro run. It seems like an easy fix but I'm struggling. Any help would be appreciated as my boss is really wanting this enhancement. Code below

Sub HideAllRows()
'Hides everything without a quantity in column H. Used for all three sales teams as well as Bid Desk when hiding everything if we've added something custom
Unprotect Password:="Test"
For Each cell In Range("H18:H458")
cell.EntireRow.Hidden = cell.Value = 0
Next cell

allowformatingcells = true (this is the line that I'm playing with that isn't working)

Protect Password:="Test"
End Sub

Thanks a bunch for any help provided

 

[julhs]

Go to the Review tab, click “Protect Sheet” then tick whatever boxes you are allowing users to do while the Sheet is protected

 

[Candyman8019]

Try adding this line:
ActiveSheet.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

 

[Dspace]

Thanks Candyman8019! that did the trick. I had to add that line of code after the password protect. Much appreciated.

 

[Dspace]

After further testing I don't think this solution fully solves my problem.

If I enter that line of code before the the password protect it will not allow highlighting.
If I enter that line of code after the password protect it allows highlighting but the worksheet is not protected.

Any ideas?
Thanks a bunch.

 

[Candyman8019]

Remove your old “protect” line. You should only require one of them.

 

[Candyman8019]

Essentially they should be combined:
ActiveSheet.Protect password:=“Test”, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

 

[Dspace]

That did the trick. Thank you! Just needed to combine them to put the password protection within the code you provided like below. Again, much appreciated.

ActiveSheet.Protect Password:="Test", AllowFormattingCells:=True