This should be an easy one

[pepsicube]

What I'm after is:-

1st I want to click & drag to select a range. (Easy)

2nd with that range selected I want to click a macro button which will merge the sected cells into one, change the background colour & add a user input comment.

Thanks

 

[njimack]

Sub Macro1()
'Your code to select range goes here
Selection.Merge
With Selection.Interior
.ColorIndex = 3 'choose number for appropriate colour
.Pattern = xlSolid
End With
Range("A1").AddComment
Range("A1").Comment.Visible = False
Range("A1").Comment.Text Text:="User Name:" & Chr(10) & "Comment Text" & Chr(10) & ""
End Sub

 

[pcc]

try this

Sub Macro2()

   r = Selection.Row: nr = Selection.Rows.Count
      c = Selection.Column: nc = Selection.Columns.Count
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = True
    End With
    Range(Cells(r, c), Cells(r + nr - 1, c + nc - 1)).Select
    
    Cells(r, c).AddComment
   Cells(r, c).Comment.Visible = False
    Cells(r, c).Comment.Text Text:="hello world"
 Cells(r, c).Interior.ColorIndex = 4 ' change to suit
   
End Sub

 

[Cbrine]

Here's some code that will do as you requested. You will need to tweak the inputbox comment, and the colorindex value.

Sub MergeCells()
Dim Cell As Range, FirstCell As Range, First As Boolean, Contents As String

First = True
For Each Cell In Selection
    If First = True Then
        Set FirstCell = Cell
        First = False
    End If
    Contents = Contents & " " & Cell
Next Cell
Selection.ClearContents
Selection.Interior.ColorIndex = 12
FirstCell = Contents & " " & InputBox("User Comment")
End Sub

HTH
Cal

 

[pepsicube]

njimack - thanks but it adds the comments in cell A1 - the comment being "user name" & "comment text"

pcc - similar but it adds the comment "hello world" - (in the correct location)

Cbrine - doesn't seem to work.

What I 'm after is the comment to be user input - ie it will vary each time

Thanks

 

[pcc]

Sub test_code()


   r = Selection.Row: nr = Selection.Rows.Count
      c = Selection.Column: nc = Selection.Columns.Count
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = True
    End With
    Range(Cells(r, c), Cells(r + nr - 1, c + nc - 1)).Select
    
    Cells(r, c).AddComment
   Cells(r, c).Comment.Visible = False
    Cells(r, c).Comment.Text Text:=InputBox("Please enter comment")
 Cells(r, c).Interior.ColorIndex = 4 ' change to suit
    
End Sub

 

[pepsicube]

Perfect - Thanks

Now just to be awkward, how easy would it be to return a merged cell back to normal, with the comment deleted (to allow for accidental incorrect input)?

 

[pcc]

use the macro recorder to generate the code and edit as necessary - sorry I'm a bit tied up at present

 

[pepsicube]

Sorted it thanks