Recorded macro suddenly stops working - breaks on color selection

[passinthru]

Hi, folks. Some weeks ago I recorded a very simple macro to conditionally format some cells. It's been working fine. Today, it's broken.

It was recorded, and is being used, on the same machine; Win7 64bit, and I'm using Excel 2010. No changes that I know of.

When I run the macro (from a button on the ribbon), it stops with the error message, "Run-time error '1004': Application-defined or object-defined error"

Here's the code:

Sub AA_cond_format_column_less_than_50()
'
' AA_cond_format_column_less_than_50 Macro
'

'

    Columns("F:F").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=50"
    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").Select
End Sub

When I choose to debug, it highlights this line:

.Color = -16383844

I've tried re-recording it, and I get the same results. It breaks on the same line.

Has anyone a clue why this is suddenly not working? How can I fix this?

 

[Norie]

You should only really use the recorder to get the syntax of the code for what you want to do.

Once you've got that you should clean it up and/or adapt it for your purpose.

The following code will apply the conditional formatting to column F in the active sheet.

    With Columns("F:F")
    
        .FormatConditions.Delete
        
        
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
                              Formula1:="=50"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority

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

        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With

        .FormatConditions(1).StopIfTrue = False

    End With

I've added a piece of code to delete any existing conditional formatting, the original code would keep adding the same rule over and over again.

In fact that might be what caused the error, though there could be other reasons.

 

[passinthru]

Thank you very much, Norie. Your code works perfectly. I've used it several times, with no errors.

Better yet, I find it educational.


I still don't understand why my recorded code worked for several weeks, then quit.

As to applying the same formatting... I use the code once on each of several worksheets in a workbook. Typically I have from 6 to 18 sheets, each of which gets the formatting in turn, one at a time.

By the way, I like your country's motto!

 

[Norie]

Thanks.

I can't explain why it failed either.

I tried running the code in a loop, without the delete part) to see if there was some limit to the no of rules.

The first time I did that Excel broke, probably because I tried to loop something like 10000 times.

I've tried cutting that down but all it seems to do is get slower not error.

 

[jbusby]

i am gettin a simular issue when i run my code i get the same error but on the code

.TintAndShade = 0

what i am trying to do is select the button and format it

bellow is the whole code

  Range("I1:J1").Select
    ActiveSheet.Buttons.Add(423, 7.5, 72, 72).Select
    Selection.OnAction = _
        "'Busby''s Utility Templates and Formatting.xlsb'!MCRAGAFormat.MCRAGAFormat"
    ActiveSheet.Shapes.Range(Array("Button 2")).Select
    'Font
    With Selection.Font
        .Name = "Times New Roman"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    'Button Alignment
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Orientation = xlHorizontal
        .AutoSize = True
    End With
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = False
    End With
     Selection.Characters.Text = "Format AGA"
    Rows("1:1").RowHeight = 41.25
    Columns("A:A").ColumnWidth = 16.14
    ActiveSheet.Shapes("Button 2").IncrementLeft -129.75
    ActiveSheet.Shapes("Button 2").IncrementTop -77.25
    ActiveSheet.Shapes("Button 2").ScaleWidth 0.5065502183, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Button 2").ScaleHeight 0.4513283627, msoFalse, _
        msoScaleFromTopLeft

 

[RoryA]

Remove that line and the one below it - they won't work on a Forms button.