Microsoft Office Tutorials and References
In Depth Information
Conditional color formatting
Figure 16-5: Using a macro to apply colors to columns based on their value
The macro to change the column colors follows:
Sub AdjustChartColors()
Dim Ser As Series
Dim Pt As Point
Dim ColorBins As Range, cell As Range
Dim i As Long
Set Ser = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
For i = 1 To Ser.Points.Count
Ser.Points(i).Interior.ColorIndex = xlNone
For Each cell In Range(“D2:D7”)
If Ser.Values(i) >= cell Then
Ser.Points(i).Interior.ColorIndex = _
cell.Offset(0, 1).Interior.ColorIndex
End If
Next cell
Next i
End Sub
The AdjustChartColors procedure uses two loops. The outer For-Next loop
cycles through each Point object. The inner For Each-Next structure loops
through each cell in the bins range in column D. If the data point is greater than or
equal to the cell value, the ColorIndex property of the Interior object of the
Point object is set to the corresponding color.
Search JabSto ::

Custom Search