Macro failing at conditional formatting font colour

Hi there,

I've recorded a macro in Excel 2007 which opens a file then highlights columns I to CH, then applies conditional formatting so that if a cell has a value of <0, it applies a red font with pink fill colour. Here's the code:

Formula:

Sub highlight()

'

' highlight Macro

'



'

    ChDir "S:\Reports"

    Workbooks.Open Filename:="S:\Reports\78weeks.xls"

    Columns("I:I").Select

    ActiveWindow.ScrollColumn = 8

    ActiveWindow.ScrollColumn = 9

    ActiveWindow.ScrollColumn = 12

    ActiveWindow.ScrollColumn = 15

    ActiveWindow.ScrollColumn = 27

    ActiveWindow.ScrollColumn = 29

    ActiveWindow.ScrollColumn = 37

    ActiveWindow.ScrollColumn = 40

    ActiveWindow.ScrollColumn = 50

    ActiveWindow.ScrollColumn = 54

    ActiveWindow.ScrollColumn = 59

    ActiveWindow.ScrollColumn = 60

    ActiveWindow.ScrollColumn = 62

    Columns("I:CH").Select

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _

        Formula1:="=0"

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Font

        .Color = -16383844

        .TintAndShade = 0

    End With

    With Selection.FormatConditions(1).Interior

        .PatternColorIndex = xlAutomatic

        .Color = 13551615

        .TintAndShade = 0

    End With

    Selection.FormatConditions(1).StopIfTrue = False

    Range("A1:A2").Select

    ActiveWorkbook.Save

    ActiveWindow.Close

End Sub

The problem is, when I run it, it fails and hitting debug takes me to the below lines, highlighted.

Formula:

With Selection.FormatConditions(1).Font

        .Color = -16383844

If anyone could point me in the right direction it would be greatly appreciated.

Try this:

Sub highlight()

'

' highlight Macro

    Workbooks.Open Filename:="S:\Reports\78weeks.xls"

    With Columns("I:CH")

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority

        With .FormatConditions(1)
            With .Font
                .Color = 393372
                .TintAndShade = 0
            End With

            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 13551615
                .TintAndShade = 0
            End With

            .StopIfTrue = False

        End With

    End With

    Range("A1:A2").Select

    ActiveWorkbook.Save

    ActiveWindow.Close

End Sub

A couple of questions:
1. Is the worksheet protected in any way?
2. Do you have at least SP2 installed on your Office 2007?

I can't see why it wouldn't work then - it works fine in 2010 (using your original code also). Can you post a sample workbook that it fails on? (no data necessary)

Noting that your workbook is in xls format, I assume you are aware that since an xls file colour palette can only use 56 colours, you may not get the exact colour you specify.