The use of conditional formatting is great for presenting data via worksheet cells in a "chart" form. A simple model is probably the best way to illustrate this concept. Assume you're working in a business with a high degree of seasonal variation. In this example, a clinic in Phoenix, Arizona currently runs three MRI scanners. The manager of the Radiology Department believes that they need another one but can't tell when. Volumes are high in the winter months because patients from the northern U.S. go to the southwest during the winter. The volumes taper off in the summer months because those same patients go back home to beat the desert heat. Each scanner can handle six exams per workday. However, downtime due to maintenance makes each scanner dependable at approximately 65% capacity. There are an average of 22 workdays per month and the expected annual exam growth is 3%.

Given the assumptions above, a simple model can be built that visually shows the seasonal variations.

By applying conditional formatting to the data table, you can easily see when the volumes exceed the average expected exams per month. The conditional formatting formula for the data table monthly spread looks like this:

The average annual expected volumes in column Q use the same conditional formatting formula except the formatting is set to red as opposed to tan.

The conditional formatting allows you to easily see that the demand for Radiology exams will exceed the capacity of the three machines starting in February of 2006. On an average annual basis, the total demand for exams will exceed the total machine capacity in 2010.

Given the visual that the conditional formatting provides, a more educated decision can be made concerning when to purchase the next machine.

A recent post to the Microsoft Excel Charting Discussion Group asks . . . I have a series of embedded charts in a workbook. Some contain data, some do not. How can I automatically print only those charts with data? The following macro loops through each embedded chart in the workbook. For each embedded chart, it loops through each series recording the point values for those series. After looping through all of the series, if the total point values are not equal to zero, the chart prints. If the total point values are equal to zero, the macro proceeds to the next chart without printing.

Sub PrintChartsWithValues()

    Application.ScreenUpdating = False

    Dim hSheet As Worksheet
    Dim Ws As Worksheet
    Dim Cht As ChartObject
    Dim Srs As Series
    Dim Pt As Points
    Dim nPts As Long
    Dim iPt As Long

    Set hSheet = ActiveSheet

    For Each Ws In Worksheets
        For Each Cht In Ws.ChartObjects
            Y = 0
            For Each Srs In Cht.Chart.SeriesCollection
                With Srs
                    nPts = .Points.Count
                    For iPt = 1 To nPts
                        X = Srs.Values(iPt)
                        Y = X + Y
                    Next
                End With
            Next Srs
            If Y <> 0 Then Cht.Chart.PrintOut Copies:=1
        Next Cht
    Next Ws

    hSheet.Select
    Range("A1").Select

End Sub

I always seem to spend ten minutes trying to find these snippets of code on the Internet when I need them. For reference . . .

To trigger a macro on a change in range, load the procedure below into a sheet module. It will fire when a change is made to the range A1:A5.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
        Your Code Here
    End If
End Sub

To trigger a macro on a change in the sheet, load the procedure below into a sheet module. It will fire when a change is made to any cell on the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Your Code Here                
End Sub

A recent post to the Microsoft Excel Charting Discussion Group asks . . . how can I change the point size of all XY chart values that are duplicates? Assuming an embedded XY chart, I responded with the procedure below. Activate (click on) the embedded chart and run the macro:

Sub ShowXYDuplicatePoints()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Srs As Series
    Dim Pt As Points
    Dim nPts As Long, iPt As Long
    Dim Test As Variant
    Dim UniqueValues As New Collection

    Set Cht = ActiveChart

    For Each Srs In Cht.SeriesCollection
        With Srs
            nPts = .Points.Count
            For iPt = 1 To nPts
                Test = Srs.XValues(iPt) & Srs.Values(iPt)
                UniqueValues.Add Acct, CStr(Test)
                On Error GoTo ErrHandler:
                If iPt + 1 > nPts Then
                    ActiveChart.Deselect
                    Exit Sub
                End If
                Label1:
            Next
        End With
    Next Srs

ErrHandler:
    Srs.Points(iPt).MarkerSize = 10
    Srs.Points(iPt).MarkerBackgroundColorIndex = 3
    Srs.Points(iPt).MarkerForegroundColorIndex = 3
    If iPt + 1 > nPts Then
        ActiveChart.Deselect
        Exit Sub
    End If
    Resume Label1:

End Sub

The procedure works by looping through all of the point values within each series in the chart. As the macro loops, each point value is loaded into the UniqueValues array. Because the UniqueValues array only accepts unique values, any duplicates are automatically kicked out and run through the error handling procedure.

The formatting can be reset with the following procedure.

Sub ResetXY()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Srs As Series
    Dim Pt As Points
    Dim nPts As Long, iPt As Long
    Set Cht = ActiveChart

    For Each Srs In Cht.SeriesCollection
        With Srs
            nPts = .Points.Count
            For iPt = 1 To nPts
                Srs.Points(iPt).MarkerSize = 5
                Srs.Points(iPt).MarkerBackgroundColorIndex = 11
                Srs.Points(iPt).MarkerForegroundColorIndex = 11
            Next
        End With
    Next Srs

    ActiveChart.Deselect

End Sub

A recent post to the Microsoft Excel Charting Discussion Group asks . . . how can I change the pattern of a column based on that column's value. Assuming an embedded chart, I responded with the following procedure:

Sub ChangePatterns()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Srs As Series
    Dim Pts As Points

    Set Cht = ActiveChart
    Set Srs = Cht.SeriesCollection(1)
    Set Pts = Srs.Points

    Cnt = 1

    For Each Pt In Srs.Values

        Srs.Points(Cnt).Select

        'Sales greater than 10000
        If Pt > 10000 Then

            With Selection
                .Fill.Visible = True
                .Fill.Patterned Pattern:=msoPatternWideUpwardDiagonal
                .Fill.ForeColor.SchemeColor = 42
                .Fill.BackColor.SchemeColor = 34
            End With

        'Sales less than or equal to 10000
        ElseIf Pt <= 10000 Then

            With Selection
                .Fill.Visible = True
                .Fill.Patterned Pattern:=msoPatternLightHorizontal
                .Fill.ForeColor.SchemeColor = 43
                .Fill.BackColor.SchemeColor = 22
            End With

        End If

        Cnt = Cnt + 1

    Next Pt

    ActiveChart.Deselect

End Sub

-- Just thought I would document it for my own reference. Is there a better way to write this?

Microsoft's macrofun.hlp file documents the XLM function GET.CHART.ITEM, which is useful for finding the vertical and horizontal positions for selected chart items. This function is further explained in Professional Excel Development.

From Microsoft's macrofun.hlp, below is the documentation for GET.CHART.ITEM:

GET.CHART.ITEM returns the vertical or horizontal position of a point on a chart item. Use these position numbers with FORMAT.MOVE and FORMAT.SIZE to change the position and size of chart items. Position is measured in points; a point is 1/72nd of an inch.

Syntax

GET.CHART.ITEM(x_y_index, point_index, item_text)

X_Y_index is a number specifying which of the coordinates you want returned.

X_Y_index Coordinate returned:

• 1 - Horizontal coordinate.
• 2 - Vertical coordinate.

Point_index is a number specifying the point on the chart item. These indexes are described later. If point_index is omitted, it is assumed to be 1. If the specified item is a point, point_index must be 1. If the specified item is any line other than a data line, use the following values for point_index.

Point_index and Chart item position:

• 1 - Lower or left.
• 2 - Upper or right.

If the selected item is a legend, plot area, chart area, or an area in an area chart, use the following values for point_index.

Point_index and Chart item position:

• 1 - Upper left.
• 2 - Upper middle.
• 3 - Upper right.
• 4 - Right middle.
• 5 - Lower right.
• 6 - Lower middle.
• 7 - Lower left.
• 8 - Left middle.

If the selected item is an arrow in Microsoft Excel 4.0, use the following values for point_index. In Microsoft Excel 5.0, arrows are named lines, and the arrowhead position returned is equivalent to the end of a line where the arrowhead begins.

Point_index and Chart item position:

• 1 - Arrow shaft.
• 2 - Arrowhead.

If the selected item is a pie slice, use the following values for point_index.

Point_index and Chart item position:

• 1 - Outermost counterclockwise point.
• 2 - Outer center point.
• 3 - Outermost clockwise point.
• 4 - Midpoint of the most clockwise radius.
• 5 - Center point.
• 6 - Midpoint of the most counterclockwise radius.

Item_text is a selection code that specifies which item of a chart to select. See the chart form of SELECT for the item_text codes to use for each item of a chart.

If item_text is omitted, it is assumed to be the currently selected item.

If item_text is omitted and no item is selected, GET.CHART.ITEM returns the #VALUE! error value.

Remarks

If the specified item does not exist, or if a chart is not active when the function is carried out, the #VALUE! error value is returned.

Examples

The following macro formulas return the horizontal and vertical locations, respectively, of the top of the main-chart value axis:

GET.CHART.ITEM(1, 2, "Axis 1")

GET.CHART.ITEM(2, 2, "Axis 1")

You could then use FORMAT.MOVE to move a floating text item to the position returned by these two formulas.