Formatierung von Zellen

Die Formatierung von Zellen ist mit Hilfe von VBA-Makros ebenso leicht durchzuführen, wie mit Excel selbst.
Am nachfolgenden Beispiel wird erklärt, wie definierte Zellen mit einem Befehl formatiert werden.

Anforderung

  • Formatierung, die durch den Standard-Excel Benutzer nicht geändert werden kann, also sehr schnell wieder hergestellt werden kann.
  • Immer drei nebeneinanderliegende Zellen sollen mit einer unterschiedlichen Hintergrundfarbe formatiert werden.

Beispiel

Das Beispiel baut teilweise auf auf.

Im Screenshot ist die Zeile, die mit dem Makro benannt werden soll farblich hervorgehoben.

In diesem Beispiel wird die neue Benennung der Zellen aufgegriffen, deren Vorteile die bereits im Artikel ausführlich erläutert wurde.

Code: Orange Formatierung

Private Sub KapFarben_orange()                              
' Formatiert die Zellen: Hintergrund, Schriftfarbe, fett

    Range("Kap_7_3:Kap_7_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_5_3:Kap_5_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_3_3:Kap_3_1").Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With
    
    Range("Kap_1_3:Kap_1_1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 45
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
    Selection.Font.Bold = True
    End With


Die einzelnen Punkt der Formatierung sind ab Besten mit dem Makro-Rekorder aufzuzeichnen und dann anzupassen. Eine manuelle Eingabe ist hier wenig sinnvoll.

Der Code ist in 4 Teile getrennt.

  • Range(„Kap_7_3:Kap_7_1“)
  • Range(„Kap_5_3:Kap_5_1“)
  • Range(„Kap_3_3:Kap_3_1“)
  • Range(„Kap_1_3:Kap_1_1“)

Das bedeutet nicht anderes als die Zusammenfassung der Bereichen Kap_7_3, Kap_7_2, Kap_7_1 zu („Kap_7_3:Kap_7_1“) usw. Grundlage bildet die Benennung der Zellen von C7 zu Kap_7_3.
Die nachfolgenden Abschnitte sind identisch, da die gleiche Formatierung für alle 4 Bereiche gleich sein soll, deshalb sind sie einfach zu kopieren. Die Range ist aber abzuändern.

Nach dem fertigstellen der Makros kann es mit F5 ausgeführt werden. Die Zellen sollten sich dann automatisch formatieren.

Für die blau eingefärbten Zellen ist analog zu verfahren.
➡ Makro-Rekorder
➡ Code anpassen

Verknüpfung

Im Artikel wurde beschrieben, wie die Zellen mit Hilfe eines VBA.Makros beschrieben werden können. Es bietet sich nun an, beide Makros zu verknüpfen. Beide lassen sich sehr leicht verknüpfen. Die Reihenfolge im nachfolgenden Code ist

  1. Beschriftung
  2. Formatierung
    1. Code

      
      Option Explicit
      
      
      Sub KapBennennung()
      
      Dim strRegal7 As String             ' Zur Beschriftung der Zellen mit Regal X
      strRegal7 = "Regal 7"
      
      Dim strRegal6 As String             ' Zur Beschriftung der Zellen mit Regal X
      strRegal6 = "Regal 6"
      
      Dim strRegal5 As String             ' Zur Beschriftung der Zellen mit Regal X
      strRegal5 = "Regal 5"
      
      Dim strRegal4 As String             ' Zur Beschriftung der Zellen mit Regal X
      strRegal4 = "Regal 4"
      
      Dim strRegal3 As String             ' Zur Beschriftung der Zellen mit Regal X
      strRegal3 = "Regal 3"
      
      Dim strRegal2 As String             ' Zur Beschriftung der Zellen mit Regal X
      strRegal2 = "Regal 2"
      
      Dim strRegal1 As String             ' Zur Beschriftung der Zellen mit Regal X
      strRegal1 = "Regal 1"
      
      Dim strNum1 As String               ' Zur Beschriftung der Zellen mit -1
      strNum1 = "-1"
      
      Dim strNum2 As String               ' Zur Beschriftung der Zellen mit -2
      strNum2 = "-2"
      
      Dim strNum3 As String               ' Zur Beschriftung der Zellen mit -3
      strNum3 = "-3"
      
      
      Sheets("Kapazitaet").Select
      
          Range("Kap_7_3").Value = strRegal7 & strNum3    ' Beschriftet die Zelle Kap_7_3 --> Regal 7-3
          Range("Kap_7_2").Value = strRegal7 & strNum2    ' Beschriftet die Zelle Kap_7_2 --> Regal 7-2
          Range("Kap_7_1").Value = strRegal7 & strNum1    ' Beschriftet die Zelle Kap_7_1 --> Regal 7-1
          
          Range("Kap_6_3").Value = strRegal6 & strNum3
          Range("Kap_6_2").Value = strRegal6 & strNum2
          Range("Kap_6_1").Value = strRegal6 & strNum1
      
          Range("Kap_5_3").Value = strRegal5 & strNum3
          Range("Kap_5_2").Value = strRegal5 & strNum2
          Range("Kap_5_1").Value = strRegal5 & strNum1
          
          Range("Kap_4_3").Value = strRegal4 & strNum3
          Range("Kap_4_2").Value = strRegal4 & strNum2
          Range("Kap_4_1").Value = strRegal4 & strNum1
              
          Range("Kap_3_3").Value = strRegal3 & strNum3
          Range("Kap_3_2").Value = strRegal3 & strNum2
          Range("Kap_3_1").Value = strRegal3 & strNum1
          
          Range("Kap_2_3").Value = strRegal2 & strNum3
          Range("Kap_2_2").Value = strRegal2 & strNum2
          Range("Kap_2_1").Value = strRegal2 & strNum1
          
          Range("Kap_1_3").Value = strRegal1 & strNum3
          Range("Kap_1_2").Value = strRegal1 & strNum2
          Range("Kap_1_1").Value = strRegal1 & strNum1
      
      Call KapFarben_orange
      Call KapFarben_blau
      
          Range("A1").Select
          
      End Sub
      
      Private Sub KapFarben_orange()                              ' Formatiert die Zellen: Hintergrund, Schriftfarbe, fett
      
          Range("Kap_7_3:Kap_7_1").Select
              With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = 45
              .ThemeColor = xlThemeColorAccent6
              .TintAndShade = -0.249977111117893
              .PatternTintAndShade = 0
          End With
          With Selection.Font
              .ColorIndex = xlAutomatic
              .TintAndShade = 0
          End With
          With Selection.Interior
          Selection.Font.Bold = True
          End With
          
          Range("Kap_5_3:Kap_5_1").Select
              With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = 45
              .ThemeColor = xlThemeColorAccent6
              .TintAndShade = -0.249977111117893
              .PatternTintAndShade = 0
          End With
          With Selection.Font
              .ColorIndex = xlAutomatic
              .TintAndShade = 0
          End With
          With Selection.Interior
          Selection.Font.Bold = True
          End With
          
          Range("Kap_3_3:Kap_3_1").Select
              With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = 45
              .ThemeColor = xlThemeColorAccent6
              .TintAndShade = -0.249977111117893
              .PatternTintAndShade = 0
          End With
          With Selection.Font
              .ColorIndex = xlAutomatic
              .TintAndShade = 0
          End With
          With Selection.Interior
          Selection.Font.Bold = True
          End With
          
          Range("Kap_1_3:Kap_1_1").Select
          With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = 45
              .ThemeColor = xlThemeColorAccent6
              .TintAndShade = -0.249977111117893
              .PatternTintAndShade = 0
          End With
          With Selection.Font
              .ColorIndex = xlAutomatic
              .TintAndShade = 0
          End With
          With Selection.Interior
          Selection.Font.Bold = True
          End With
      End Sub
      
          
      Private Sub KapFarben_blau()                                                ' Formatiert die Zellen: Hintergrund, Schriftfarbe, fett
      
          Range("Kap_6_3:Kap_6_1").Select
              With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .ThemeColor = xlThemeColorLight2
              .TintAndShade = 0.399975585192419
              .PatternTintAndShade = 0
          End With
          With Selection.Font
              .ColorIndex = xlThemeColorDark1
              .TintAndShade = 0
          End With
          With Selection.Interior
          Selection.Font.Bold = True
          End With
          
          Range("Kap_4_3:Kap_4_1").Select
              With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .ThemeColor = xlThemeColorLight2
              .TintAndShade = 0.399975585192419
              .PatternTintAndShade = 0
          End With
          With Selection.Font
              .ColorIndex = xlThemeColorDark1
              .TintAndShade = 0
          End With
          With Selection.Interior
          Selection.Font.Bold = True
          End With
          
          Range("Kap_2_3:Kap_2_1").Select
          With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .ThemeColor = xlThemeColorLight2
              .TintAndShade = 0.399975585192419
              .PatternTintAndShade = 0
          End With
          With Selection.Font
              .ColorIndex = xlThemeColorDark1
              .TintAndShade = 0
          End With
          With Selection.Interior
          Selection.Font.Bold = True
          End With
          
              Range("Kap_Summe").Select
          With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .ThemeColor = xlThemeColorLight2
              .TintAndShade = 0.399975585192419
              .PatternTintAndShade = 0
          End With
          With Selection.Font
              .ColorIndex = xlThemeColorDark1
              .TintAndShade = 0
          End With
          With Selection.Interior
          Selection.Font.Bold = True
          End With
      End Sub
      

      Mittels Call-Befehl werden die Formatierungsmakros KapFarben_orange und KapFarben_blau zusammen aufgerufen, danach wird der Cursor auf die Zelle A1 gesetzt.

Dynamischen Bereich in mehreren Tabellenblättern formatieren

Mit einem Excel-Makro lässt sich ein variabler Bereich sehr einfach formatieren.

Die Ausgangslage

  • Eine Arbeitsmappe mit mehreren Tabellenblättern
  • In jedem Tabellenblatt sind Informationen in immer dem gleichen Bereich eingetragen, z.B. B12:F
  • Die Anzahl der beschriebenen Zeilen variiert von Tabellenblatt zu Tabellenblatt

Das Ziel
Wir möchten nun in jedem Tabellenblatt den Bereich formatieren, aber allerdings nur bis zur letzten beschriebenen Zeile. Somit könnte sich ergeben

  • Tabelle1: Bereich B12:F30
  • Tabelle2: Bereich B12:F60

Das Ganze lässt sich einfach mit einem Makro erledigen.

Sub FormatierungAusgabebereich()
'
' das Makro ermittelt die letzte beschriebene Zeile im Bereich B12:N und formatiert diesen
'
'
Dim letzte As Long
    letzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
   
   
   Range("B12:n" & letzte).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

Die Formatierung kann man sich auch mühsam manuell eintragen, jedoch würde ich empfehlen, die Aufzeichnungsfunktion zu nutzen.

Damit man sich ein bisschen Arbeit spart, kann man das obige Makro nur einmal definieren und dann über ein Makro, dass die jeweilige Tabelle auswählt, aufrufen lassen.

Tabelle 1

Sub Tabelle1
   Sheets("Tabelle1").Select
   Call FormatierungAusgabebereich
End Sub

Tabelle 2

Sub Tabelle1
   Sheets("Tabelle2").Select
   Call FormatierungAusgabebereich
End Sub