none
How to fix Run-Time error '5' in Excel RRS feed

  • Question

  • I ran this with Excel 2016, the following errors occurred:

    Run-time error '5' and the debug shows to line in bold as the source of error:

     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=0.0", Formula2:="=0.649999"
        Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlColorIndexNone
            .ColorIndex = 3
        End With
        Selection.FormatConditions(1).StopIfTrue = True
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=0.65", Formula2:="=0.799999"
        Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlColorIndexNone
            .ColorIndex = 6
        End With

    Please advise how to fix this

    Sunday, October 8, 2017 4:47 PM

All replies

  • Kenutari,
    re:  conditional formatting code

    First:  the bold code line does not appear (nothing is bold)
    Second:  the code runs without a problem in XL2010
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Sunday, October 8, 2017 5:07 PM
  • Thanks Jim,

    I put it bold as it was highlighted as the issue, it wasn't in bold.

    I am using MacBook Pro running on macOS High Sierra, the error message was:

    Run time error '5'

    How shall I fix this?

    Cheers,

    Kenutari

    Monday, October 9, 2017 12:34 AM
  • Also using Excel 2016 version
    Monday, October 9, 2017 12:37 AM
  • Kenutari,
    re:  error 5

    I was expecting you would identify the code line causing the error.
    You won't get much help unless you do.
    You should also include all of the code for the function or sub.

    Error 5 is "Invalid procedure call or argument" and the part you omitted may be the culprit.
    The Excel help file for error 5 states:
      "An argument probably exceeds the range of permitted values."
      "This error can also occur if an attempt is made to call a procedure that isn't valid on the current platform."

    I don't use a Mac and the xl2016 version for the Mac is a work in progress.
    I've read that a Microsoft Excel MAC update with some missing parts is due soon.
    If the issue is something unique to a Mac I won't be much help.
    '---
    Jim Cone

    Monday, October 9, 2017 1:03 AM
  • Hi Kenutari,

    first I would like to inform you that we are not available with Mac machines to test the code on Mac.

    we have Windows machines installed with Excel 2016.

    if you just try to specify the problematic line then we can try to make a test with that line on Windows machine with Excel 2016 to reproduce the issue.

    it is also fine if you are able to post the whole code or your workbook.

    we will try to make a test with it.

    I can see that you had posted the several lines of code.

    if you tell us which line is causing the error then we will try to provide you further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 9, 2017 2:12 AM
    Moderator
  • Jim/Deepak,

    This is highlighted code error:

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=0.0", Formula2:="=0.649999"

    Thanks 

    Kenneth

    Tuesday, October 10, 2017 9:04 AM
  • Kenneth,
    Re:  error 5 on Mac

    Unable to help.
    '---
    Jim Cone
    Tuesday, October 10, 2017 3:16 PM
  • Hi Kenutari,

    I try to check the same line of code on Windows Excel 2016.

    it is working fine without any error.

    if you are available with other version of Excel then I suggest you to make a test with it.

    it is possible that this issue occurs due to specific update or issue is there in this specific version of Excel.

    if your code works correctly in other version of Excel then we can verify this.

    then you can try to install the latest update if any new update is available.

    if no any new update is available then you can try to remove the current update and move to last working update to temporary solve the issue.

    then when new update launched you can install it.

    also let me know if the same code was working fine for you before or you develop this new code and got the error when you try to run it.

    let me know if this helps you to solve your issue or not.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 11, 2017 7:26 AM
    Moderator
  • Hi Kenutari,

    is your issue solved now?

    if yes, try to post the solution and mark it as an answer.

    it will help us to close this thread and in future it will help other users who have same kind of issue like yours.

    if your issue is still exist then try to refer my last suggestions. it may help you.

    if then also you have any further questions regarding this issue then let me know about that.

    I will try to provide you further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 18, 2017 5:31 AM
    Moderator