Excel crashes when running macro after having changed interior color

[Telefonstolpe]

Hi,

If I manually change interior color on a cell in the worsheet and afterwards run a macro excel crashes.
It happens on most of the macros i'm using.
Anybody who has any idea about what the issue can be?

I'll include a very simple macro here as an example:

Sub PRI_AntalJustera_()Dim rad As Long
Dim kGrupp As Long
Dim kMärke As Long
Dim kTyp As Long
Dim kAntal As Long
Dim RngGrupp As Range
Dim Ruta As Range


'Förbered XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'On Error GoTo Av****a
Application.ScreenUpdating = False
Application.EnableEvents = False
ThisWorkbook.Activate
Blad1.Select
'Ta bort protection när makro körs
'---------------------------------------------------------------------------


'Set variables XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
rad = Selection.Row
kGrupp = Range("PRIk_Grupp").Column
kMärke = Range("PRIk_Märke").Column
kTyp = Range("PRIk_Typ").Column
kAntal = Range("PRIk_Antal").Column
Set RngGrupp = Range("PRIk_Grupp")
'---------------------------------------------------------------------------


'Villkor XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
If Selection.Rows.Count <> 1 Then GoTo Fel1
If Intersect(Selection, Range("Tabell1")) Is Nothing Then GoTo Fel1
If Cells(rad, kTyp) = "Rubrik" Then GoTo Av****a
If Cells(rad, kTyp) = "Handbok" Then GoTo Fel2
'---------------------------------------------------------------------------


'JUSTERA ANTAL XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


'Lägg till eventuella handböcker *********
If Cells(rad, kAntal) < 1 And PubAntal < 1 Then GoTo Av****a


If Cells(rad, kTyp) = "Printer" Then
If Cells(rad, kMärke) = "Ricoh" Then


    For Each Ruta In RngGrupp
    If Ruta.Value = Cells(rad, kGrupp) Then
    If Cells(Ruta.Row, kTyp) = "Handbok" Then


        Cells(Ruta.Row, kAntal) = Cells(Ruta.Row, kAntal) + PubAntal
        If Cells(Ruta.Row, kAntal) < 1 Then Cells(Ruta.Row, kAntal) = ""
        GoTo Nästa


    End If
    End If
    Next Ruta


End If
End If


Nästa:
'*****************************************


'Justera vald cell ***********************
Cells(rad, kAntal) = Cells(rad, kAntal) + PubAntal
If Cells(rad, kAntal) < 1 Then Cells(rad, kAntal) = ""
'*****************************************


'---------------------------------------------------------------------------


'Fel XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
GoTo Av****a:
Fel1:
'Application.ScreenUpdating = True
MsgBox "Du måste markera max en rad i tabellen"
GoTo Av****a
Fel2:
'Application.ScreenUpdating = True
MsgBox "Du kan inte välja antal handböcker. Antalet handböcker läggs automatiskt till när du lägger till skrivare"
GoTo Av****a
'---------------------------------------------------------------------------


'Av****a XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Av****a:
Application.ScreenUpdating = True
Application.EnableEvents = True
'---------------------------------------------------------------------------
End Sub

/Chris

 

[Jeffrey Mahoney]

Nothing in your code gives me an indication that it is working with interior colors. You say it crashes with other macros also.

I have not seen that before. The only thing I can think is that Excel has run out of resources. "Too many cells got formatted". It just happens to crash during a macro because more memory is needed. Sorry I can't come up with anything else.

Jeff

 

[Telefonstolpe]

Hi Jeff,

Thank you for taking your time!

I've been running basically hundreds of macros per day on this computer for a couple of years without this happening. My memory usage is not larger now than before, so this is really strange...

/Chris

 

[MARK858]

Crashes on what line and what does the error message state?

 

[Telefonstolpe]

So far I havent been able to determine on what line or what type of command it is that makes it crash, but I'm working on it.
If I step through the macro with F8 it doesn't crash.

The message I get is that Excel has stopped working, and then it closes...



/Chris

 

[MARK858]

That message does sound like it is having a resource issue. Post back when you find a specific line/error issue.

 

[Telefonstolpe]

That message does sound like it is having a resource issue. Post back when you find a specific line/error issue.

Ok, Thanks!

 

[Jeffrey Mahoney]

Do you know how to debug the code one line at a time? I've actually seen Excel crash without showing the line that is causing the problem. Also, Do you have any statements in your code like:

On Error Resume Next

If so, remove them and see what you get.

Jeff

 

[Jeffrey Mahoney]

I do see lines such as:

On Error Goto ???

Those are going to potentially prevent you from seeing the actual line that causes the issue