... und sie dreht sich doch!
26.05.2019 12:50:36
Rainer
Hallo Marc,
Peter Kloßek hat super Arbeit abgeliefert!
Mich hat es aber auch nicht so ganz in Ruhe gelassen.
So kann mein Makro auch Treemap Charts färben:
Sub Conditional_Format_for_Graphs_V2()
'Select a Series from a Chart, the Points will be coloured according to the Backgraund Colour
'Also works with Conditional Formating of the Cells
'Tested for Point Charts and Column Charts
'Update: Treemap works now
Dim RaZelle As Range
On Error Resume Next
test = ActiveChart.Name
If IsEmpty(ActiveChart.Name) Then
MsgBox "Select Graph first", vbCritical
Exit Sub
End If
If TypeName(Selection) = "Series" Then
Set ch = ActiveChart.SeriesCollection
For SeriesI = 1 To ch.Count
If ch(SeriesI).Name = Selection.Name Then I = SeriesI
Next SeriesI
Else
MsgBox "Select Series first", vbCritical
Exit Sub
End If
SeriesI = I
SeriesFormula = ActiveChart.SeriesCollection(SeriesI).Formula
X1 = InStr(SeriesFormula, ",") 'Ende Name
X1 = InStr(X1 + 1, SeriesFormula, ",") 'Ende X-Achse
X2 = InStr(X1 + 1, SeriesFormula, "!") 'Ende Blattname
X1 = InStr(X1 + 1, SeriesFormula, ",")
ValueRange = Mid(SeriesFormula, X2 + 1, X1 - X2 - 1)
If ValueRange = "" Then 'Treemap
Dim chtObj1 As ChartObject
Dim chtObj2 As ChartObject
Set chtObj1 = ActiveChart.Parent
Set chtObj2 = chtObj1.Duplicate.Chart.Parent
chtObj2.Activate
ActiveChart.ChartType = xlLine
SeriesFormula = ActiveChart.SeriesCollection(1).Formula
X1 = InStr(SeriesFormula, ",") 'Ende Name
X1 = InStr(X1 + 1, SeriesFormula, ",") 'Ende X-Achse
X2 = InStr(X1 + 1, SeriesFormula, "!") 'Ende Blattname
X1 = InStr(X1 + 1, SeriesFormula, ",")
ValueRange = Mid(SeriesFormula, X2 + 1, X1 - X2 - 1)
ActiveChart.Parent.Delete
chtObj1.Activate
ActiveChart.FullSeriesCollection(1).Select
End If
I = 1
For Each RaZelle In ActiveSheet.Range(ValueRange)
If RaZelle.DisplayFormat.Interior.Color 16777215 Then
ActiveChart.SeriesCollection(SeriesI).Points(I).Format.Fill.ForeColor.RGB = _
RaZelle.DisplayFormat.Interior.Color
End If
I = I + 1
Next RaZelle
End Sub
Du musst aber darauf achten, dass dein Treemap auch wirklich nur eine Datenreihe hat. In deiner Beispieldatei hatte es 3 Reihen, wovon aber nur eine dargestellt wurde.
Gruß, Rainer
