マクロ
エクセル

[エクセルマクロ]PC変わったらまず設定するやつ

TODO

まだまだあった・・・😢あとでかくリスト
・行列をコピーして挿入(Ctrl+shift+P)
・値だけコピー(Ctrl+shift+C)
・書式だけ貼り付け(Ctrl+shift+V)

PCを変えたときにまず設定したいやつ。

・デフォルトで作成されるシート数を1つに設定
・デフォルトフォントをメイリオに設定
・開発タブ出す
・VBA保存 ⇒ ショートカット設定

文字サイズを大きく(Ctrl+Shift+L)

Sub 文字を大きく
'
' 文字を大きく Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    MyFontSize = Selection.Font.Size
    Selection.Font.Size = MyFontSize + 1

End Sub

文字サイズを小さく(Ctrl+Shift+K)

Sub 文字を小さく
'
' 文字を小さく Macro
'
' Keyboard Shortcut: Ctrl+Shift+K
'
    MyFontSize = Selection.Font.Size
    Selection.Font.Size = MyFontSize - 1

End Sub

文字色を赤にする(Ctrl+Shift+R)

Sub 赤色()
'
' 赤色 Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
End Sub

背景色を黄色にする(Ctrl+Shift+Y)

Sub 黄色()
'
' 黄色 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

グレーアウト(Ctrl+Shift+G)

Sub 灰色()
'
' 灰色 Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
End Sub

書式リセット(Ctrl+Shift+B)

Sub 書式リセット()
'
' 書式リセット Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    Selection.ClearFormats

End Sub

罫線を引く(Ctrl+Shift+T)

  • 見出しをつくる
    • 青い背景色に白い色の文字
    • 文字はセンタリング
  • 罫線を引く
  • No列を設定

キャプチャ.PNG

Sub 罫線()
'
' 罫線 Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    ' No を入れる
    Dim no As Range
    Dim i As Integer
    For Each no In Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row + Selection.Rows.Count - 1, Selection.Column))
      no.Value = i
      i = i + 1
    Next no
    ' No 列
    Cells(Selection.Row, Selection.Column).Value = "No"

    ' header
    Selection.Resize(1).Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Sub