Double conditional formatting

[khansen]

Hello,

I would like to create a graphic in a small space. I would like to use a bar chart to display the magnitude of a measurement in one cell and the reading for that measurement (neighboring cell) would determine the color. For starters I could bin the measurements into 5 values to make life easier. It seems like the new conditional formatting in excel 2007 or 2010b would work well for this but I don't know how to conditionally format based on a neighboring cell or combine conditional formats.

Cheers!
Kirk

[Andy Pope]

Use a chart with multiple series, one for each colour required.

Set the overlap to 100%
Reduce gap width to 10%

[khansen]

Fantastic solution! This has really made my day!!!

Kirk

[khansen]

Hello,

I am trying to make your solution into a macro and am having some trouble..

Based on a solution to an earlier questions I have this VBA code that appears to run OK.

Sub MakeCharts()

    Dim rngDataA As Range
    Dim rngOutputA As Range
    Dim chtTemp As Chart
    Dim lngZoom As Long
    
    lngZoom = ActiveWindow.Zoom
    ActiveWindow.Zoom = 100
    
    With ActiveSheet
        Set rngDataA = .Range("AZ2:BF19")
        Set rngOutputA = .Range("BG3:BH19")

    End With
    
    Do While Len(rngDataA.Cells(1, 1)) > 0
        With rngOutputA
            Set chtTemp = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width, .Height).Chart
            With chtTemp
                .SetSourceData rngDataA
                .ChartType = xlBarClustered
                .HasLegend = False
                .Axes(xlValue).MaximumScale = 1
                .Axes(xlCategory).ReversePlotOrder = True
                .Axes(xlCategory).TickLabelPosition = xlNone
                .Axes(xlCategory).MajorTickMark = xlNone
                .ChartGroups(1).Overlap = 100
                .ChartGroups(1).GapWidth = 13
                .ChartGroups (1)
                .Axes(xlValue).TickLabelPosition = xlNone
                .Axes(xlValue).MajorTickMark = xlNone
            End With

        End With
    
        Set rngDataA = rngDataA.Offset(19)
        Set rngOutputA = rngOutputA.Offset(19)
    
    Loop
    ActiveWindow.Zoom = lngZoom
    
End Sub

Then I need to change the color of ".ChartGroups (1)" then 2, 3, 4...
I am having a hard time with the syntax to do this..

When I record a macro I get:

Sub Macro24()
'
' Macro24 Macro
'

'
    ActiveSheet.ChartObjects("Chart 117").Activate
    ActiveChart.SeriesCollection(3).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(245, 210, 210)
        .Solid
    End With
    ActiveChart.SeriesCollection(4).Select
    Selection.Format.Line.Visible = msoFalse
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(220, 247, 209)
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
End Sub

I haven't been able to integrate this with the code above..

The other thing that I want to do is to remove the "chart area" border and fill.

Suggestions?

What is a good reference for learning VBA syntax?

Thank you!

[Andy Pope]

This should do all the creation and formatting.

Sub MakeCharts()

    Dim rngDataA As Range
    Dim rngOutputA As Range
    Dim chtTemp As Chart
    Dim lngZoom As Long
    
    lngZoom = ActiveWindow.Zoom
    ActiveWindow.Zoom = 100
    
    With ActiveSheet
        Set rngDataA = .Range("AZ2:BF19")
        Set rngOutputA = .Range("BG3:BH19")

    End With
    
    Do While Len(rngDataA.Cells(1, 1)) > 0
        With rngOutputA
            Set chtTemp = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width, .Height).Chart
            With chtTemp
                .SetSourceData rngDataA
                .ChartType = xlBarClustered
                .HasLegend = False
                With .Axes(xlCategory)
                    .ReversePlotOrder = True
                    .TickLabelPosition = xlNone
                    .MajorTickMark = xlNone
                    .Format.Line.Visible = False
                End With
                With .ChartGroups(1)
                    .Overlap = 100
                    .GapWidth = 13
                End With
                With .Axes(xlValue)
                    .MaximumScale = 1
                    .TickLabelPosition = xlNone
                    .MajorTickMark = xlNone
                    .Format.Line.Visible = False
                    .HasMajorGridlines = False
                End With
                With .ChartArea.Format
                    .Fill.Visible = False
                    .Line.Visible = False
                End With
                With .PlotArea.Format
                    .Fill.Visible = False
                    .Line.Visible = False
                End With
            
                With .SeriesCollection(3).Format.Fill
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(245, 210, 210)
                    .Solid
                End With
                With .SeriesCollection(4).Format.Fill
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(220, 247, 209)
                    .Solid
                End With
            End With

        End With
    
        Set rngDataA = rngDataA.Offset(19)
        Set rngOutputA = rngOutputA.Offset(19)
    
    Loop
    ActiveWindow.Zoom = lngZoom
    
End Sub

The help and object browser are the best/only sources of vba syntax for charts in xl2007.
I haven't read John Walkenbach's 2007 chart book but that may contain some helpful info on charts+vba

[khansen]

Wow! This is fantastic.. Thank you very much Andy!