Selecting, coloring and defining a range


Active Member
Hi everyone,

For an app under development, I need the user to:
1) Select a range of rows within a single column (say, A5:A15)
2) Color the background of the selected range.
3) Click on a button that opens a UserForm where the selected range is shown ( e.g. within a text box in the UDF, labeled Selected Range:) as "A5:A15" and its new color kept.

The active macro would then merge the cells within the range, write a certain label on it and save the workbook. Further workbook openings will show the range with the written words within and the color background changed.

In researching the web, I came across to this snippet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.FormatConditions.Add xlExpression, , "TRUE"
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
The only thing this allows is to change the color, but it retains neither the selected range color nor its address when clicking the button calling the active macro.

I will appreciate immensely if a modification to above or an alternative code is suggested to attain the sequence of events mentioned above.
Thanks, as usual

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...