Results 1 to 10 of 10
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    ColorIndex Problem (v5, v7, 2000)

    As a VBA newbie and trying to learn I Wrote a macro to change background and font colour to white so they do not print. When run the following error message appears "Run Time Error 1004" Unable to set the ColorIndex property of the Interior class.
    Initially when I wrote/recorded the macro in Excel 2000 it runs OK. I then save as v5 so other users can run. When testing on V5 this is the error message. The relevant code for the macro is
    Range("L1").Select
    Selection.Font.ColorIndex = 2
    With Selection.Interior
    .ColorIndex = 2
    .Pattern = xlSolid
    End With.
    I initially thought that the problem might be with the index codes. I recorded the same steps on a V5 PC and the code was the same as that above, so I am now (again) confused.
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: ColorIndex Problem (v5, v7, 2000)

    pccoyle

    If version 5 is going to be used, i suggest you also develop in version 5.

    Record the same macro in version 5 and see the difference.

    HTH.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: ColorIndex Problem (v5, v7, 2000)

    Hi Wassim,
    When I did my testing after the first instance, I recorded the same steps on a V5 PC, then compared the code. The recorded code on the V5 PC was the same as the original! hence my <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: ColorIndex Problem (v5, v7, 2000)

    Pardon the dumb question, but you mean Excel 5, not VB 5? For me the following works in XL97, but I don't have XL5 or XL95 to test on.

    Sub colorL1()
    With Range("L1")
    .Font.ColorIndex = 2
    .Interior.ColorIndex = 2
    .Interior.Pattern = xlSolid
    End With
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: ColorIndex Problem (v5, v7, 2000)

    Sorry John,
    My fault for not being specific, I am referring to Excel V5. I have tried your code is a blank sheet as a test, as I could follow what you were doing (I think). However the error message is now sub or Function not defined, so again my newbieness (!) is showing. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: ColorIndex Problem (v5, v7, 2000)

    I'm probably not going to be able to help, since I don't have XL5 installed anywhere. However, perhaps someone else can if you post the entire sub, show exactly which line errors out (usually highlighted in yellow), and the exact error message.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: ColorIndex Problem (v5, v7, 2000)

    John,
    Here is a sheet with the problem, Many thanks for this.
    Paul Coyle
    Approach love and cooking with reckless abandon

  8. #8
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: ColorIndex Problem (v5, v7, 2000)

    John,
    Here is the file that I thought I posted previously
    Attached Files Attached Files
    Paul Coyle
    Approach love and cooking with reckless abandon

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ColorIndex Problem (v5, v7, 2000)

    I am afraind something is wrong with your file. It indeed does not work, but if I try out exactly the same code in another workbook, then there is no problem. Your file might be corrupted.

  10. #10
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: ColorIndex Problem (v5, v7, 2000)

    Hans, your discovery appears to be the cause. I deleted the original macro, resaved the file, re did the macro and wonder of wonders, all was well.
    Many thanks to you and John for your help.
    <img src=/S/joy.gif border=0 alt=joy width=23 height=23><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •