CODEWITHSUNDEEP
vbaexcel
Sandy
Sandy

Reputation: 27

Macro not selecting range for comment change in Excel

I am trying to write a macro for Excel 2010 that will take a user selection of a cell or range of merged cells and unbold the comment and add a pattern.

Sub commentstripe()
'
' commentstripe Macro
'
'
    Dim myRange As Range
    Set myRange = Range(Selection.Address)
    myRange.Comment.Shape.TextFrame.Characters.Font.Bold = False
    With myRange.Interior
        .Pattern = xlLightUp
        .PatternColorIndex = xlAutomatic
        .PatternTintAndShade = 0
    End With
    ActiveWorkbook.Save
End Sub

It seems to be hanging on the myRange.comment line but I can't seem to figure out why. I'm running it from a shortcut, Ctrl+Shift+A. Any advice would be appreciated.

Regards, Sandy

Upvotes: 1

Views: 54

Answers (1)

Andres Felipe Martinez
Andres Felipe Martinez

Reputation: 323

I don't see why it would hang... except for the fact that if there's no comment in the cell, it will throw an error. Also, if this is a new workbook, and you just save it, it will throw a warning saying this is an excel file, not a macro-enabled excel file. Just for you to know.

I commented the save line and everything worked perfectly.

EDIT: See if this works

Sub commentstripe()

Dim myRange As Range
Set myRange = Selection
myRange.Cells(1, 1).Comment.Shape.TextFrame.Characters.Font.Bold = False
With myRange.Interior
    .Pattern = xlLightUp
    .PatternColorIndex = xlAutomatic
    .PatternTintAndShade = 0
End With
ActiveWorkbook.Save
End Sub

Upvotes: 1

Related Questions