Conditional Formats in Charts

**Jazzy Max** · 09-15-2009, 08:53 PM

Well I was trying to change the columns that go red, the switch between red and black.

I have tried entering 'Green' where it says 'Red' (in the macro) I have tried:

With ActiveSheet.ChartObjects(1).Chart
' make both bars the same
.SeriesCollection(2).Format.Fill.ForeColor.RGB (155,0,2) = .SeriesCollection(1).Format.Fill.ForeColor.RGB (160,8,5)

Just random colours for now, but the columns that I wanted to change weren't changing..

**Andy Pope** · 09-16-2009, 04:13 AM

That line does not make sense.

To set the colour of a column use

.SeriesCollection(2).Format.Fill.ForeColor.RGB  = RGB(155,0,2)

.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(160,8,5)

**Jazzy Max** · 09-16-2009, 10:00 PM

that is changing the colour for the Income column.

I wanted to make the 'Expense' column (Which currently switches between Red and black) to follow the conditional format that I had set in cells B32, C32and B33, C33

Maybe I didn't explain myself properly..sorry

**Andy Pope** · 09-17-2009, 05:39 AM

Try this,

Sub FormatChart()

    Dim lngPoint As Long
    Dim objSeries1 As Series
    Dim objSeries2 As Series
    
    With ActiveSheet.ChartObjects(1).Chart
        ' make both bars the same
        Set objSeries1 = .SeriesCollection(1)
        Set objSeries2 = .SeriesCollection(2)
        objSeries1.Format.Fill.ForeColor.SchemeColor = 17
        objSeries2.Format.Fill.ForeColor.SchemeColor = objSeries1.Format.Fill.ForeColor.SchemeColor
        For lngPoint = 1 To .SeriesCollection(1).Points.Count
            If Application.WorksheetFunction.Index(objSeries2.Values, lngPoint) < _
               Application.WorksheetFunction.Index(objSeries1.Values, lngPoint) Then
               ' make expense Red
               objSeries2.Points(lngPoint).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            End If
        Next
    End With
    
End Sub

**Jazzy Max** · 09-17-2009, 11:06 PM

that's very good Andy, you are too good!

but its when 'Mr A's' expenses are greater than his income, the expenses stay green...so its kind of inverted.

Again I have tried, and that doesn't work

This is what I am after.

MR A Columns

Income = $60,000
Expense = $61,000

Then expense column goes red.

Income = $60,000
Expense = $60,000 (or below)

the expense column stays green...

Is there a code for that?

**Andy Pope** · 09-18-2009, 04:20 AM

Yes you just need to add the code to test the values the other way around.

Sub FormatChart()

    Dim lngPoint As Long
    Dim objSeries1 As Series
    Dim objSeries2 As Series
    
    With ActiveSheet.ChartObjects(1).Chart
        ' make both bars the same
        Set objSeries1 = .SeriesCollection(1)
        Set objSeries2 = .SeriesCollection(2)
        objSeries1.Format.Fill.ForeColor.SchemeColor = 17
        objSeries2.Format.Fill.ForeColor.SchemeColor = objSeries1.Format.Fill.ForeColor.SchemeColor
        For lngPoint = 1 To .SeriesCollection(1).Points.Count
            If Application.WorksheetFunction.Index(objSeries2.Values, lngPoint) < _
               Application.WorksheetFunction.Index(objSeries1.Values, lngPoint) Then
               ' make expense Red
               objSeries2.Points(lngPoint).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            End If
            If Application.WorksheetFunction.Index(objSeries1.Values, lngPoint) < _
               Application.WorksheetFunction.Index(objSeries2.Values, lngPoint) Then
               ' make expense Red
               objSeries1.Points(lngPoint).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            End If
        Next
    End With
    
End Sub