Microsoft Excel

Herbers Excel/VBA-Archiv

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

Farbskale pro Zeile für sehr viele Zeilen

nach unten

Betrifft: Farbskale pro Zeile für sehr viele Zeilen von: Michi K
Geschrieben am: 13.10.2020 16:53:32


Hallo zusammen,
ich bin ganz neu hier aber hoffe, dass ihr mir vielleicht trotzdem helfen könntet. Ich habe fast keine VBA-Erfahrung, daher tu ich mir ein wenig schwerer. Habe schon wirklich lange nach einer Lösung gesucht, aber keine hat bei mir bisher funktioniert.

Folgende Voraussetzungen/Ziele habe ich:
- Pro Zeile 5 Werte (nicht nebeneinander)
- Diese 5 Werte pro Zeile sollen mit Hilfe der bedingten Formatierung (Farbskala) eingefärbt werden: grün für den kleinsten Wert, rot für den höchsten Wert und einen Farbverlauf über Gelb bei den drei mittleren Werten
- Mehrere hundert Zeilen insgesamt

Wenn ich in der bedingten Formatierung einfach den Wertebereich ändere, dann werden halt die Werte aller Zeilen verglichen und nicht immer nur die Werte einer Zeile. Vielleicht kann man das ja auch irgendwie ändern, dann müsste ich nicht mit Makros arbeiten.

Dann bin ich durch Recherche auf Makros gekommen. Ich dachte, ich erstelle einmal ein Makro, wo ich in einer Zeile den Farbverlauf mache und dann Loope ich das quasi. Also dann ein For-Loop von 1 bis 1000. Aber irgendwie kann ich den Code nicht so abändern, dass die Schleife funktioniert.

Hier mal der Code einer Beispieldatei:

Sub Makro5()
'
' Makro5 Makro
'

'
    Range("A1,C1,E1,G1,I1").Select
    Range("I1").Activate
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 192
        .TintAndShade = 0
    End With
End Sub

So würde ich dann die Schleife einbauen. Meine Änderungen sind fett formatiert.


Sub Makro5()
'
' Makro5 Makro
'

'
    Dim i As Long
    For i = 1 To 5
    
    Range(Cells(i, 1), Cells(i, 3), Cells(i, 5), Cells(i, 7), Cells(i, 9)).Select
  
    
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 192
        .TintAndShade = 0
    End With
    
    Next
End Sub


Leider weiß ich nicht, wie ich das .activate mit einer Variable angeben kann. Daher habe ich es gelöscht.
Folgende Fehlermeldung erhalte ich:


Fehler beim Kompilieren.:

Falsche Anzahl von Argumenten oder ungültige Eigenschaftenzuweisung.
Danke für eure Hilfe. Wenn es ohne Makro geht freue ich mich, obwohl mich die richtige Lösung mit dem Loop doch auch sehr interessieren würde. Vielen Dank!

nach oben  nach unten

Betrifft: AW: Farbskale pro Zeile für sehr viele Zeilen
von: ralf_b
Geschrieben am: 13.10.2020 16:57:11

zeig deine Formel für die bedingte Formatierung und welcher Zellbereich genau ist gemeint?

nach oben  nach unten

Betrifft: AW: Farbskale pro Zeile für sehr viele Zeilen
von: Michi K
Geschrieben am: 13.10.2020 17:12:58

Danke für deine Antwort.
Die zu vergleichenden Werte in der ersten Zeile stehen in A1, C1, E1, G1 und I1.
In der nächsten Zeile geht es dann um die Werte in A2, C2, E2, G2 und I2.

Ich habe ganz klassisch die 3-Farben-Skala (dementsprechend keine Formel verfügbar) genutzt. Der Wertebereich ist dementsprechend für Zeile 1 wie folgt: $A$1;$C$1;$E$1;$G$1;$I$1

nach oben  nach unten

Betrifft: AW: Farbskale pro Zeile für sehr viele Zeilen
von: ralf_b
Geschrieben am: 13.10.2020 17:33:32

1. du kannst die nicht zusammenhängenden Bereiche mit STRG-Taste einzeln auswählen und zum Bereich in der bed. Formatierung zusammenführen.

2. Bei Regel bearbeiten steht auch ein Feld für Typ. dort kann man auch Formel auswählen. im Feld "Wert" läßt sich eine Formel angeben.

nach oben  nach unten

Betrifft: AW: als Sub()
von: Fennek
Geschrieben am: 13.10.2020 17:26:27

Hallo,

in einer Sub() ist das recht übersichtlich:
' nach kkleinste einfärben

Sub T_1()
Dim rng As Range
Dim Col As Range
Set Col = Range("A:A, C:C, E:E, G:G, I:I")

For i = 1 To 100
    Set rng = Intersect(Rows(i), Col)
    For k = 1 To 5
        zz = WorksheetFunction.Small(rng, k)
        adr = Application.Match(zz, Rows(i), 0)
        Select Case k
            Case Is = 1: Cells(i, adr).Interior.Color = vbRed
            Case Is = 2: Cells(i, adr).Interior.Color = 6740479
            Case Is = 3: Cells(i, adr).Interior.Color = 10086143
            Case Is = 4: Cells(i, adr).Interior.Color = 13431551
            Case Is = 5: Cells(i, adr).Interior.Color = vbGreen
        End Select
    Next k
Next i
End Sub
mfg