保護されているシートでマクロ実行するとエラー

質問

Excel2010で勤務表を作っています。

A列にとある文字列（承認）と入力すると、その行が保護されるマクロを、下記URLからコピペして使わせて頂いてます。
http://questionbox.jp.msn.com/qa3277541.html

勤務表なので、土日祝日は網掛けになるよう条件付き書式を使っています。
休暇取得した場合は網掛けを付けて、休日出勤した場合には網掛けなしにしたり出来るようマクロをマクロの自動記録で作りました。

ところが、どこかの行が保護されている状態で、セルの網掛けを変更するマクロを実行すると
「実行時エラー'1004'アプリケーション定義またはオブジェクトの定義エラーです」と出てしまいます。
保護されている行ではなく、まだ保護はされていない行に実行しています。
エラーが出ているのは網掛けマクロから出ています。

以下、今エクセルファイルにあるマクロの構文になります。

網掛けマクロは全部で4つ作りました。

１）休日出勤した際に条件付き書式をクリアして網掛けなしにするマクロです。
Sub 休日出勤()
' 条件書式クリア
Selection.FormatConditions.Delete
End Sub
※エラーになっている部分です。

２）平日休んだ日に網掛けをするマクロです。
Sub 休日()

' 網掛け

With Selection.Interior
.ColorIndex = 0
.Pattern = xlGray16
.PatternColorIndex = xlAutomatic
End With
End Sub

３）２）のマクロで休日にしたけど、やっぱり出勤したという時に、１）だと網掛けなしにならなかったので、網掛けなしにするマクロを作りました。
Sub網掛けなし()

' 網掛けなし

With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

４）ごちゃごちゃいじってしまって最初の状態に戻したいと思ったので条件付き書式を再設定するマクロを作りました。が、２）の休日マクロを実行したセルは元に戻らないので仕方なく３）の網掛けなしマクロを実行しなければなりません。

Sub 書式クリア()

' 条件書式再設定

Range("A6:K36").Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WEEKDAY($B6,2)>=6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGray16
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(WEEKDAY($B6)=1,COUNTIF(祝日,$B6))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGray16
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

※この中のSelection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WEEKDAY($B6,2)>=6"の部分がエラーと出ています。

※１）から３）は網掛けしたいところ、網掛けなしにしたいところを範囲選択してから実行しなければなりません。

その他、保護を解除する際にパスワード認証が欲しかったので、「保護解除」ボタンを押すためにパスワード認証させるマクロもあります。
これは特に問題なく動いています。

５）パスワード認証つき保護解除マクロ
Sub password()
Dim pw As Long
pw = Application.InputBox( _
prompt:="パスワード入力", Type:=1)
If pw <> "123" Then
MsgBox "パスワードが違います"
Exit Sub
Else
MsgBox "保護解除しました"
ActiveSheet.Unprotect

End If
End Sub

６）行ごとに保護するマクロです。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r, rng As Range
Set rng = Intersect(Target, Columns(1))
If Not rng Is Nothing Then
If ActiveSheet.ProtectContents = True Then

ActiveSheet.Unprotect

End If
For Each r In rng
If r.Value = "承認" Then
r.EntireRow.Locked = True
Else
r.EntireRow.Locked = False
End If
Next r
ActiveSheet.Protect DrawingObjects:=True, Contents:=True
End If
End Sub

マクロに関してはド素人で、自動記録かWebで調べて見つけたマクロをちょっと加工して使う程度です。

どうか知恵をお貸しください。よろしくお願いします。

回答No.1

kuwa_98 · ベストアンサー

シートをプロテクトするとき、書式の変更を可能として下さい。
プロテクトする行を下記に書き換えて下さい。

ActiveSheet.Protect DrawingObjects:=True, Contents:=True_
, AllowFormattingCells:=True

お礼

ご回答ありがとうございました。
書き換えたところ無事に網掛けマクロが動きました！
本当にどうもありがとうございました。