Microsoft Office Tutorials and References
In Depth Information
Conditional color formatting
Sub ApplyRandomGradient
Dim GrStyle As Long, GrVariant As Long
Dim FColor As Long, BColor As Long
‘Get the random values
GrStyle = Int(Rnd * 7) + 1
If GrStyle = 6 Then GrStyle = 1
If GrStyle = 7 Then
GrVariant = Int(Rnd * 2) + 1
Else
GrVariant = Int(Rnd * 4) + 1
End If
FColor = Int(Rnd * 57) + 1
BColor = Int(Rnd * 57) + 1
‘Apply the colors
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
.Fill.TwoColorGradient Style:=GrStyle, Variant:=GrVariant
.Fill.ForeColor.SchemeColor = FColor
.Fill.BackColor.SchemeColor = BColor
End With
End Sub
This macro generates some interesting color combinations — some good, some
bad, and some downright ugly!
This macro uses VBA’s Rnd function, which generates a random number
between 0 and 1. To generate random integers between 1 and n, combine
the Rnd function with the Int function:
Int(Rnd * n) +1
Conditional color formatting
In Chapter 8 I describe a technique to apply different colors to columns in a column
chart, based on the value of the data point. The example in this section uses a macro
to accomplish the same effect.
Figure 16-5 shows a chart that displays 30 columns. In addition, the cells in
column D contain bins that determine which colors to use (the colors are displayed in
the cells in column E). Each value in column D represents the lower value range.
For example, a value greater than or equal to 0 (cell D2) but less than 10 (cell D3) is
assigned the color in cell E2.
Search JabSto ::




Custom Search