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"
<stvgarner.26f2fe_1145290805.034@excelforum-nospam.com> wrote in
message
news:stvgarner.26f2fe_1145290805.034@excelforum-nospam.com...
>
> Any idea what I am doing wrong here:
>
> 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
>