RefEdit with a color frame-type selecting tool


New Member
Aug 26, 2011
It's my first time posting on this forum, but I couldn't find an answer anywhere else and it got me really frustrated.
Is it possible to somehow create and control the colorful range selection frames that appear when you type formulas with references to cell addresses?
For instance when you type "=A1" in a cell, a blue frame will appear around A1, which you can then drag around.

I have a userform with 4 separate RefEdit input boxes and I really wish the user could clearly see all the ranges he selected.
I tried to work around it by executing code which changed the background of a range in the RefEdit_change event (first changing back the background of the cells it marked before). The code was as follows:

Dim OldRng As Range
Private Sub RefEdit1_Change()
If Not OldRng Is Nothing Then OldRng.Interior.ColorIndex = 0
On Error GoTo Errorhandler:
Set NewRng = Range(RefEdit1.Value)
NewRng.Interior.ColorIndex = 3
Set OldRng = NewRng
On Error GoTo 0
Exit Sub
Exit Sub
Resume Next
End Sub

It more or less works, but I feel like I'm trying to brake down an open door here. Does anyone have any tips?
Any help much appreciated, if you don't feel like typing an entire reply but know what i'm talking about please just post the proper name of the tool i'm trying to learn to use in this thread so I know what to google for.

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Watch MrExcel Video

Forum statistics

Latest member