Run-time Error 1004 problems

Question

I am working on a macro code that will Highlight selected cells in a protected sheet. Here is the code that i have figured out on my own.

Sub HighlightCells()

' Sheet.Unprotect Password:="****"

'

' HighlightCells Macro

' Highlight selected cells

'

' Keyboard Shortcut: Ctrl+h

'

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 65535

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

' Sheet.Protect Password:="****"

End Sub

I keep getting the Run-time error '1004': Application-defined or object-defined error. When I go to the debug, it highlights the ".Pattern = xlSolid" part of the code. I am not sure what to do to fix this and can't seem to find an answer to how to generally fix this. Can someone give me a hand here?

Answer 1

Hello GeoffGoodenough! I'm Jen, an independent advisor and a fellow Microsoft user like you. I'd be happy to help you out with this issue.

Glad to know you have figured out the solution to the problem. Thanks for getting back to us and letting us know. Stay safe!

Best Regards,

Jen :)

Answer 2

Sorry just figured it out had the wrong unprotected/protect coding.

Here is the new code: 

Sub HighlightCells()

'

' HighlightCells Macro

' Highlight selected cells

'

' Keyboard Shortcut: Ctrl+h

'

    ActiveSheet.Select

    ActiveSheet.Unprotect Password:="****"

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 65535

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    ActiveSheet.Select

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _

        False

End Sub

This works and does what i wanted.