• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel crashing when using copy or cut in areas where conditional formatting with custom function is applied.

Devil02

New Member
Hello all,
I have been using this custom function to automatically highlight off days columns in timesheet except for cells that already have fill color. It used to work fine but I recently moved to excel 2016 from 2013. I am not sure but I think it worked for some time in excel 2016 too but now whenever I copy or cut any cells or range including one row above and below where this custom function is used to apply conditional formatting, excel crashes. For example, In the attached workbook I have applied conditional formatting to $5:$10. And if I try to copy or cut cells from $4:$11, excel just crashes. Sometimes, excel will restart after crashing and sometimes it will just stay closed.

Here's the custom function:

>>> use code - tags <<<
Code:
Option Explicit
Function ISNOFILL(Optional Rng As Range) As Boolean
Application.Volatile
ISNOFILL = False
If Rng Is Nothing Then
If TypeOf Application.Caller Is Range Then
    If Application.Caller.Interior.ColorIndex = xlNone Then
    ISNOFILL = True
    Else
    ISNOFILL = False
    End If
End If
Else
    If Rng.Interior.ColorIndex = xlNone Then
    ISNOFILL = True
    Else
    ISNOFILL = False
    End If
End If
End Function
Any help will be appreciated!
 

Attachments

  • Sample.xlsm
    14.8 KB · Views: 3
Last edited by a moderator:
This worked for me with no crashing...
Code:
Function ColorTest(C As Range) As Boolean
    Application.Volatile
    ColorTest = C.Interior.ColorIndex = -4142
End Function

and the conditional formula as
Code:
=ColorTest(A5)
 
This worked for me with no crashing...
Code:
Function ColorTest(C As Range) As Boolean
    Application.Volatile
    ColorTest = C.Interior.ColorIndex = -4142
End Function

and the conditional formula as
Code:
=ColorTest(A5)
Well, even your version of the code didn't work. It's still crashing. What version of office do you have? I have even tried repairing office installation thinking it was broken but no luck there either.
 
No problem here in O365.
Consider perhaps using
VarType(Application.Caller)
or
TypeName(Application.Caller)
 
Back
Top