Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    VBA to color Active.Cell with Arrow key syntax

    Although it's part of a recorded macro to obtain the color number, is there anything else required in the VBA coding ?
    It has to use the ActiveCell.Offset(0, -2) part because the Cell location is not constant, it is where it is at the time.
    run time error 438- Object does not support this property or method
    Application.DisplayAlerts = False
    ActiveCell.Offset(0, -2).Color = 65535 'yellow
    Thanks

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    XP,

    Amend your code to:
    Code:
    Sub Macro1()
    Application.DisplayAlerts = False
    ActiveCell.Offset(0, -2).Interior.Color = 65535 'yellow
    End Sub
    You will receive an error if the column number of the active cell is 1 or 2 because of your offset to the left.

    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    XPDiHard (2015-06-14)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi XP

    If you are using Excel2007 or later, the attached file may be useful.
    It has the 'color number' for vba names you can use in your code.
    These are sorted alphabetically by name, and by 'color number' value.

    So, for example, you could use
    Code:
    ActiveCell.Offset(0, -2).Interior.Color = rgbYellow
    or
    Code:
    ActiveCell.Offset(0, -2).Interior.Color = rgbGold
    ..What I would like is for someone to sort this colour list into 'rainbow' order!
    Maud - fancy the challenge? Anyone?

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    XPDiHard (2015-06-14)

  6. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,

    I'll leave that for you since you'll probably figure out some vba code to do it automatically.

  7. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Trap the error:
    Code:
    Sub Macro1()
         Application.DisplayAlerts = False
         With ActiveCell
               if .Column() > 2 then _
                 .Offset(0, -2).Interior.Color = 65535 'yellow
         End With  'ActiveCell
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    XPDiHard (2015-06-14)

  9. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    RG,

    That's the perfect way to do it!

    Zeddy,

    Here is the full spectrum of colors with their RGB and Values in increments of 10.

    Maud
    Attached Files Attached Files

  10. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    XPDiHard (2015-06-14),zeddy (2015-06-14)

  11. #7
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks


    Sub macrodfdsc()

    ' Interior.Color = 65535
    'ActiveCell.Offset(0, -2#).Interior.Color = 65535

    Selection.Offset(0, -3).Resize(1, 8).Interior.Color = 65535 '*Down*
    End Sub

    Selection.Offset(0, 1).Select '*Right*
    Selection.Offset(0, -1).Select '*Left*
    Selection.Offset(-1, 0).Select '*Up*
    Selection.Offset(1, 0).Select '*Down*

    Selection.Offset(2, 0).Resize(5, 8).Select '*Down*
    Last edited by XPDiHard; 2015-06-14 at 09:55.

  12. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..thanks for that spectrum. I'll add that to my toolbox.

    I'm posting a new thread on this.

    zeddy

Posting Permissions

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