wie kann man ein Diagramm anhand von einer bedingten Formatierung färben lassen?
Anbei meine Datei: https://www.herber.de/bbs/user/88794.xlsm
Sub DiaFaerben()
Dim lngPunkt As Long
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(2)
.ApplyDataLabels
For lngPunkt = 1 To .Points.Count
With .Points(lngPunkt)
.Interior.Color = Cells(lngPunkt + 2, 7).Interior.Color
With .Format.Fill
.Visible = msoTrue
.Transparency = 0
.Solid
End With
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Line
.Visible = msoTrue
.Weight = 2
End With
.DataLabel.Caption = "=Tabelle1!F" & lngPunkt + 2
End With
Next lngPunkt
End With
End Sub
Sub DiaFaerben()
Dim lngPunkt As Long
Dim rngZelle As Range
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(2)
.ApplyDataLabels
For lngPunkt = 1 To .Points.Count
With .Points(lngPunkt)
.DataLabel.Caption = "=Tabelle1!F" & lngPunkt + 2
Set rngZelle = Worksheets("Tabelle3").Columns(1).Find(Cells(lngPunkt + 2, 6).Value, _
lookat:=xlWhole)
If Not rngZelle Is Nothing Then
.Interior.Color = Worksheets("Tabelle3").Range(rngZelle.Address).Interior.Color
With .Format.Fill
.Visible = msoTrue
.Transparency = 0
.Solid
End With
End If
With .Format.Line
.Visible = msoTrue
.Weight = 2
End With
End With
Next lngPunkt
End With
End Sub
Option Explicit
Sub DiaFaerben()
Dim lngPunkt As Long
Dim strBeruf As String
Dim lngColor As Long
Dim R As Integer
Dim G As Integer
Dim B As Integer
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(2)
For lngPunkt = 1 To .Points.Count
strBeruf = Sheets("Tabelle1").Cells(lngPunkt + 2, 6).Value
On Error Resume Next
lngColor = Sheets("Tabelle3").Cells(Application.Match(strBeruf, Sheets("Tabelle3") _
.Range("A2:A11"), 0) + 1, 1).Interior.Color
liesRGB lngColor, R, G, B
With .Points(lngPunkt).Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(R, G, B)
.Transparency = 0
.Solid
End With
Next
End With
End Sub
Function liesRGB(lngColor As Long, ByRef Red As Integer, _
ByRef Green As Integer, ByRef Blue As Integer)
On Error Resume Next
Red = lngColor Mod 256
lngColor = (lngColor - Red) / 256
Green = lngColor Mod 256
lngColor = (lngColor - Green) / 256
Blue = lngColor Mod 256
End Function