|
条件式書式が設定されたセルに行を挿入したりフィルハンドルすると適用先が変わってしまいますので、マクロの自動記録で再設定できるようにしました。これに例えば10行目に一行挿入したり10行目にオートフィルをした場合に、チェンジイベントでマクロ名「Sub 条件式書式の再設定()」が自動動作するようにお願いします
チェンジイベントマクロはB列だけの5行目以降からのみに適用してください。
自動記録で取っていますのでコードが長いです
動作が同じでしたら簡略化されるとなおベストです
Sub 条件式書式の再設定()
Range("A5").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN($A5:$A2000)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B5").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN($B5:$B2000)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.CutCopyMode = False
Cells.FormatConditions.Delete
Range("A5").Select
Range("B5").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN($A5:$A2000)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B5:B2000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISERROR(FIND(""("",$B5:$B2000,1))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B5:B2000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$B5:$B2000="""""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B5:B2000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN($B5:$B2000)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A5:A2000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN($A5:$A2000)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Sub 一行挿入()
Range("B10").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
Sub 一行オートフィル()
Selection.AutoFill Destination:=Range("A10:B10"), Type:=xlFillDefault
Range("A10:B10").Select
End Sub
|
|