Any idea what I am doing wrong he

Sub TestRangeValue()

Dim dValue As Range

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

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


End Sub

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

Thanks for any help

--
stvgarne
-----------------------------------------------------------------------
stvgarner's Profile: http://www.excelforum.com/member.php...fo&userid=2059
View this thread: http://www.excelforum.com/showthread.php?threadid=53346

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

That did it, Thanks!!!

Do you know if there is a way to make the range not be absolute (C7
instead of $C$7)? Not to worried about this part though.


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

ActiveCell.Value = "=TSColor(" & dValue.Address & ")"
becomes
ActiveCell.Value = "=TSColor(" & dValue.Address(0,0) & ")"

stvgarner wrote:

That did it, Thanks!!!

Do you know if there is a way to make the range not be absolute (C7
instead of $C$7)? Not to worried about this part though.

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

--

Dave Peterson

Well that looks easy enough.



Thanks!!


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