マクロでアクティブセルのある範囲をデザイン

マクロ講座64回　

表を自動でフォーマットする Excel2013マクロ講座 64回

選択セルのある表をデザイン

今回は、アクティブセルのある範囲に対して、表のデザインをするというマクロです。
使うのは、何度も登場しているCurrentRegion プロパティです。
CurrentRegionプロパティは、空白のセルで囲まれた範囲を読み取り、Range オブジェクトを返します。
また同じ対象（オブジェクト）に対して、繰り返し処理をする時に、記述を簡単にする、 With...End With Statement も使います。
（サンプルファイルは、こちらからマクロ64回サンプルデータ）

Withステートメントのネストでまとめて設定

今回は、表のデザインを設定したい表に対して、どこかのセルを選択して、アクティブにしておきます。
セルの範囲を探して表のデザインマクロ1

CurrentRegionプロパティが、空白のセルを行と列から読み取り、値の入力されていないセルで囲まれた範囲を選択します。

CurrentRegion プロパティ

この例では、範囲B1：D11を選択し、Withステートメントで設定していきます。コードは、つぎの様になります。

Sub セルのある表デザイン()
  With ActiveCell.CurrentRegion
    With .Borders ' 表全体の設定
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = 16
    End With
    With .Rows(1) ' タイトル行の設定
      .Interior.ColorIndex = 37
      .HorizontalAlignment = xlCenter
      .Borders(xlEdgeTop).Weight = xlMedium ' 上罫線を中太
      .Borders(xlEdgeBottom).Weight = xlMedium ' 下罫線を中太に
    End With
  End With
End Sub

実行すると図のようになります。表の罫線の設定は、.LineStyle = xlContinuousの部分を変更することで替えることができます。
セルの範囲を探して表のデザインマクロ2

ラインの種類はxlContinuous, 実線. xlDash, 破線. xlDashDot, 一点鎖線. xlDashDotDot, 二点鎖線. xlDot, 点線. xlDouble, 二重線. xlLineStyleNone, 線なし. xlSlantDashDot, 斜線から選ぶことができます。
セルの範囲を探して表のデザインマクロ4

どうせなら金額が入力された数値セルに対しても書式設定を行いたいですね。そこで、 SpecialCells メソッドを使って、条件に該当するセルをまとめて書式を設定するコードを追加しましょう。

.SpecialCells(Type:=xlCellTypeConstants, Value:=xlNumbers).NumberFormat = "#,##0_ "

全く同じではつまらないので、タイトル行の色を変更しています。
セルの範囲を探して表のデザインマクロ3

SpecialCells メソッドで特定のセルを探す時、該当セルがないとエラーになるのですが、プログラムを中断するほどのエラーではないので、エラー無視するようにコードを追加しています。

Sub セルのある表デザインと書式()
  With ActiveCell.CurrentRegion
    With .Borders
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = 16
    End With
    With .Rows(1)
      .Interior.ColorIndex = 23
      .HorizontalAlignment = xlCenter
      .Font.Color = vbWhite ' フォントの色を白に
    End With
    On Error Resume Next ' エラーを無視する
    .SpecialCells(Type:=xlCellTypeConstants, Value:=xlNumbers).NumberFormat = "#,##0_ "
    End With
End Sub