bedingte Formatierung via VBA

Question

Hallo liebe Excelfreunde,

ich hab da mal ne kleine Frage...

Ich möchte via VBA den Text in einem Tabellenblatt formatieren. Dabei soll die Formatierung anhang von verschiedenen Bedingungen erfolgen. D..h jede Zelle, welchen einen Text mit dem Inhalt "Komponente" enthält soll bspw. Fett, Schriftgröße 12, Linkbündig, vertikal zentriert und eine Zeilenhöhe von 20 haben.

Jetzt habe ich mit dem Recorder ein Marko aufgenommen. Damit kann ich wunderbar den Text nach die Textinhalt filtern und Fett machen. Das Problem ist nur, dass ich in der bedingten Formatierung keine Schriftgröße, Linksbündig, etc. einstellen kann. Wie bekomme ich es hin, dass der Text in Abhängigkeit von der Bedingung "Komponente" so formatiert wie ich das will?

Hier ist der Code vom Makro-Recorder:

Sub Formatierung()

Range("A1:A16000").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
Selection.FormatConditions.Add Type:=xlTextString, String:="Komponente", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
.ThemeColor = xlThemeColorDark1
End With
Selection.FormatConditions(1).StopIfTrue = False

End Sub


Kann mir da jemand weiterhelfen? Vielen Dank.

Grüße
Steffen

Answer 1

Hallo Steffen,

die entscheidende Frage dürfte sein, ob Du die 16000 Zellen unter einer vom VBA-Makro zu prüfenden Bedingung formatieren willst oder ob Du für diese Zellen eine bedingte Formatierung erstellen willst. Für letzteres gibt es einige Beschränkungen, so dass man auf die Frage: "Wie bekomme ich es hin, ... " antworten muss: anders oder gar nicht.

Hier ein Auszug aus der VBA-Hilfe zum FormatCondition Objekt:
Verwenden Sie die Add-Methode, um ein neues bedingtes Format zu erstellen. ...

Verwenden Sie die Eigenschaften Font, Border und Interior des FormatCondition-Objekts, um das Erscheinungsbild der formatierten Zellen zu steuern. Einige Eigenschaften dieser Objekte werden durch das Objektmodell des bedingten Formats nicht unterstützt.

Die Eigenschaften, die mit der bedingten Formatierung verwendet werden können, sind in der folgenden Tabelle [Anm.: mangels funktionierender Tabulation untereinander] aufgeführt.

Objekt
Eigenschaften

Font
Bold
Color
ColorIndex
FontStyle
Italic
Strikethrough
Underline
Die Unterstreichungsarten "Buchhaltung" können nicht verwendet werden.

Border
Bottom
Color
Left
Right
Style - Folgende Rahmenarten können verwendet werden (alle anderen werden nicht unterstützt): xlNone, xlSolid, xlDash, xlDot, xlDashDot, xlDashDotDot, xlGray50, xlGray75 und xlGray25.
Top
Weight - Folgende Rahmenstärken können verwendet werden (alle anderen werden nicht unterstützt): xlWeightHairline und xlWeightThin.

Interior
Color
ColorIndex
Pattern
PatternColorIndex

Schriftgröße 12, linkbündig, vertikal zentriert und Zeilenhöhe 20 können also durch eine bedingte Formatierung nicht eingestellt werden. Und das Fett könnte man sich dann eigentlich auch gleich sparen, wenn man sich sowieso einen anderen Lösungsweg überlegen muss, und dann dort mit unterbringen.

In welcher Richtung willst Du weitermachen?

MfG Charlotte

Answer 2

Hallo Charlotte,

vielen Dank für die Info. Kein wunder das es nicht funktioniert.

In welcher Richtung willst Du weitermachen?

Also wenn man sich sowieso einen anderen Lösungsweg überlegen muss, dann wäre ich dankbar, wenn du mir einen vorschlagen könntest. Ich möchte die 16000 Zellen unter einer vom VBA-Makro zu prüfenden Bedingung formatieren. Dabei habe ich bestimmte Schlüsselwörter (z.B. Komponente - welche allerdings mit mehreren Wörten in der Zelle kombiniert sind...also z.B. Komponente bla und Komponente bla bla etc.) welche gesucht und falls diese gefunden wird, formatiert werden soll (mit Schriftgröße 12, linkbündig, vertikal zentriert und Zeilenhöhe 20).

Wie mach ich das, ohne das es all zu lange dauert? Es sind nicht alle 16000 Zeilen beschrieben...maximal ca. 500. Die Anzahl der beschriebenen Zeilen ist aber variabel und hängt von einer Vorauswahl ab.

Grüße
Steffen

Answer 3

Hallo Steffen,

versuchs mal damit: in den VBA-Bereich der Tabelle(n) kopieren, bei der (denen) formatiert werden soll. Mit Suchbegriff = "Lila". Achtung: Suchbegriff ist case-sensitiv!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Bereich As Range
Dim Zelle As Range
Dim Suchbegriff As String

'Set Bereich = Range("A1:A16000") 'für den Ernstfall
Set Bereich = Range("A1:A16") 'für Testzwecke etwas kleiner
Suchbegriff = "Lila"

For Each Zelle In Range(Target.Address)
If Not Intersect(Zelle, Bereich) Is Nothing Then
If InStr(Zelle.Value, Suchbegriff) > 0 Then
Zelle.HorizontalAlignment = xlLeft
Zelle.VerticalAlignment = xlCenter
Zelle.RowHeight = 20
With Zelle.Font
.Bold = True 'fett
.Size = 12 'Größe 12
'.Name = "Arial" 'andere Schriftart
End With
Else ' wenn Suchbegriff in Zelle nicht oder nicht mehr enthalten
Zelle.HorizontalAlignment = xlHAlignGeneral 'ursprüngliche H-Ausrichtung
Zelle.VerticalAlignment = xlVAlignBottom 'ursprüngliche V-Ausrichtung
Zelle.RowHeight = 12.75 'ursprüngliche Zeilenhöhe
With Zelle.Font
.Bold = False 'nicht fett
.Size = 10 'ursprüngliche Größe
'.Name = "Arial" 'ursprüngliche Schriftart
End With
End If
End If
Next
End Sub

Alles andere dann morgen. Tschüß!
Charlotte

Answer 4

Hallo Charlotte,

ich danke Dir! Es funktioniert sehr gut. Ich musste nur etwas ändern, da er mir bei der Zeile: For Each Zelle In Range(Target.Address) die Fehlermeldung "Objekt erforderlich" angezeigt hat. Ich hab einfach anstelle des Range(Target.Address) ... For Each Zelle In Bereich geschrieben. Dann funktioniert es einwandfrei. Echt super!

Bei solchen Codes bin ich irgenwie immer hoffnungslos verloren wenn ich von Null sowas schreiben muss. Ich danke dir vielmals. Wie kommt man auf diese Codes frag ich mich immer (als Leihe ;-).

Na dann bin ich mal gespannt auf morgen :-)

Grüße
Steffen