PDA

View Full Version : macro causes Excel to 'crash'



ordeayn
01-21-2014, 02:37 AM
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
01-21-2014, 03:08 AM
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
01-21-2014, 03:10 AM
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
01-21-2014, 03:44 AM
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
01-21-2014, 05:18 AM
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
01-21-2014, 05:22 AM
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
01-21-2014, 05:30 AM
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