Formatierung Archive - Stefan's Weblog

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

Beschriftung
Formatierung

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.

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

Schlagwort: Formatierung

Formatierung von Zellen

Anforderung

Beispiel

Code: Orange Formatierung

Verknüpfung

Code

Dynamischen Bereich in mehreren Tabellenblättern formatieren