AW: Bedingte Formatierung für Diagramme
27.05.2017 11:40:06
Rainer
Hallo Hajo,
vielen Dank, das hat mir sehr geholfen.
ich versuchte für Diagramme eine bedingte Formatierung zu verwenden, aber bekam beim auslesen der Farbe immer nur "dunkelschwarz" als Ergebnis. Mit deiner Formel geht das super.
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
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)
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
Vielen Dank,
Rainer