Try

Dim dValue As Range
On Error Resume Next
Set dValue = Application.InputBox( _
prompt:="Enter Cell Reference to GetColor Value For",
Type:=8)
On Error GoTo 0
If Not dValue Is Nothing Then
ActiveCell.Value = "=TSColor(" & dValue.Address & ")"
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"stvgarner"
wrote in
message
...

Any idea what I am doing wrong he

Sub TestRangeValue()

Dim dValue As Range

Set dValue = Application.InputBox(prompt:="Enter Cell Reference
to Get
Color Value For", Type:=8)

ActiveCell.Value = "=TSColor(" & dValue & ")"


End Sub

TSColor is a function I created to pull the background color in
number
format so it could be sorted by color. I am trying to get this
to type
the formula for them (kind of a teaching thing for new
formulas) when
they select the range to use. I am getting the value in the
range I
select and not the range itself. If I enter C7 into cell C7
this works
to put the formula in cell C8 but if they select C7 and it is
blank it
returns '=TSColor()'

Thanks for any help.


--
stvgarner
------------------------------------------------------------------------
stvgarner's Profile:
http://www.excelforum.com/member.php...o&userid=20599
View this thread:
http://www.excelforum.com/showthread...hreadid=533460