+ Reply to Thread
Results 1 to 4 of 4

Changing Comment Backcolor w/VBA

Hybrid View

  1. #1
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Changing Comment Backcolor w/VBA

    Does anyone know the correct syntax for changing the back color of a comment.
    As I get older I can't tolerate light backgrounds and I've been manually changing the background of my comments to a dark color.
    I'm looking all around and I see countless examples of how to change the text and text font but nothing that addresses the back color of the comment.
    What I have below works fine on the text and the last line that is commented out is the last version I tried to change the back color.
    It throws an object doesn't support the property or method error 438.

    Sub test()
    
    Dim cmt As Comment
    Set cmt = ActiveCell.Comment
    
    With cmt.Shape.TextFrame.Characters.Font
          .Name = "Tahoma"
          .Size = 13
          .Bold = True
          .ColorIndex = 0
    End With
    
    'cmt.Shape.TextFrame.BackColor = RGB(0, 0, 50)
    
    End Sub

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Changing Comment Backcolor w/VBA

    Something you can practice with.

    cmt.Shape.Fill.ForeColor.SchemeColor = 15

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Changing Comment Backcolor w/VBA

    Dave,
    I couldn't find any documentation to tell me what numbers to use for certain colors so I used trial and error.
    It seems like some of the colors come up under more than one number.
    You saved me a ton of time, thanks very much for your help.

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Changing Comment Backcolor w/VBA

    I found some good code related to formatting comment boxes for anyone interested.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing Backcolor on activecells
    By Ernie Fenwick in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2021, 10:29 AM
  2. [SOLVED] Changing Backcolor in ActiveX Option Buttons
    By natefarm in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-30-2018, 01:53 PM
  3. changing the backcolor of a listbox
    By kweep in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-11-2015, 06:00 AM
  4. [SOLVED] Listbox Selection/Highlight is lost after changing Listbox BackColor
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2014, 10:43 PM
  5. Changing BackColor on all Forms
    By frogger750 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-07-2012, 10:20 PM
  6. Changing Button BackColor
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2011, 07:12 PM
  7. Changing BackColor on Command Buttons?
    By RBee in forum Excel General
    Replies: 0
    Last Post: 08-15-2006, 01:25 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1