Thread: Double conditional formatting

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

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

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

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!

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