Prompt for a range to apply code to

[Steph]

Hello everyone. I have a piece of code that colors the contents of a
cell if the contents are hard-coded. This code as currently written
applies to the nentire sheet. I would like to apply this code to only
a specific range of cells. This will be used by end users, so ideally
I'd like then to push a button, which will then prpomt the user via a
form of some sort to select the range of cells. Can you help? Thanks
so much in advance!! Here's what I have now:

Sub ColorCellsRange()
On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, 1).Font.ColorIndex = 5
End Sub

-Steph

 

[Harald Staff]

Hi Steph

Sub Test()
Dim R As Range
On Error Resume Next
Set R = Application.InputBox _
(Prompt:="Select a range with your mouse", _
Default:=Selection.Address, _
Type:=8)
If R Is Nothing Then Exit Sub
R.SpecialCells(xlCellTypeConstants, 1).Font.ColorIndex = 5
End Sub

HTH. Best wishes Harald

 

[Ron de Bruin]

Hi Steph

Try this one

Sub test()
Dim rng As Range
On Error Resume Next
Application.DisplayAlerts = False
Set rng = Application.InputBox("Select a range with the mouse", _
Type:=8)
Application.DisplayAlerts = True
On Error GoTo 0
If rng Is Nothing Then
MsgBox "You Press cancel"
Exit Sub
End If
On Error Resume Next
rng.SpecialCells(xlCellTypeConstants, 1).Font.ColorIndex = 5
On Error GoTo 0
End Sub