macro causes Excel to 'crash'

[ordeayn]

For i = 50 To 2 Step -1
    If (Range("O" & i).Value > 20 And Range("O" & i).Value <> "UNREQ") Then
    Rows(i).Interior.ColorIndex = 18
    Rows(i).Font.ColorIndex = 2
End If
Next i
End Sub

When running this macro the line Rows(i).Font.ColorIndex = 2 causes Excel to close down when trying to save the worksheet. Removing the one line allows saving. I am an occasional user of Excel and VBA so I would appreciate an explanation of what I am doing incorrectly, please
Many thanks

[Jan Karel Pieterse]

All I can think of is that the workbook in question has acquired some form of corruption and the change of the font color triggers the problem. I would expect manually changing the same cells font color would cause the problem too.

[GTO]

Greetings,

If (Range("O" & i).Value > 20 And Range("O" & i).Value <> "UNREQ") Then
I do not understand your test. If the value is more than 20, then it's a number in the cell; so why the compound test?

Also, when is the housing procedure being run?

Mark

[ordeayn]

Greetings,

If (Range("O" & i).Value > 20 And Range("O" & i).Value <> "UNREQ") Then
I do not understand your test. If the value is more than 20, then it's a number in the cell; so why the compound test?

Also, when is the housing procedure being run?

Mark

THis is a spreadsheet of values from a laboratory system. I run the macro which sorts the values and colors the rows according to the clinical details. The laboratory system returns a numeric value or unreq if the test was not requested. The compound statement is the only way that, with my limited knowledge, I could color the row only if the value exceeded 50 (before that it colored the unreq as well). I run the routine twice a day which is when the worksheet is updated from the laboratory system. It is also true that if I manually color or try to embolden anything on the spreadsheet, it crashes on saving as well.

[Jan Karel Pieterse]

Which -unfortunately- proves my point that the file is on the verge of collapsing.
Try if you can copy all sheets (click first, shift+click last, right-click sheet tabs and select "Move or copy") to a new empty workbook.

[ordeayn]

Which -unfortunately- proves my point that the file is on the verge of collapsing.
Try if you can copy all sheets (click first, shift+click last, right-click sheet tabs and select "Move or copy") to a new empty workbook.

Thank you. I shall do what you have suggested

[snb]

I tested:

Sub tst()
    With sheet1.Cells(2, 15).CurrentRegion.Resize(, 1)
      .AutoFilter 1, ">50"
      .Interior.ColorIndex = 18
      .Font.ColorIndex = 2
      .AutoFilter
    End With
End Sub