Option Explicit
Sub CustomPicker()

ActiveCell.Select 'why resize(1,1) and why select????

'don't include the ()'s
CustomChanges ActiveCell
'or if you want them, add the Call statement
Call CustomChanges(ActiveCell)
'don't use both.

End Sub

Function CustomChanges(xTargetCell As Range)
'do stuff
Dim a As Long
Dim b As Long

a = 2
b = 3

xTargetCell.Value = a
xTargetCell.Interior.Color = b
End Function

ker_01 wrote:

I'm trying to pass a single-cell range to a function that will then do stuff
to that cell. I've figured out how to do everything I want, with one (or two)
exceptions:

(1) I haven't figured out the proper way to pass the activecell to my
function as a range. I'm trying lots of variations of the following, but
haven't found the right syntax; in this case I'm getting type mismatch
presumably because activecell.address isn't a range (I guess it is a
string?).

Sub CustomPicker()
ActiveCell.Resize(1, 1).Select
CustomChanges(ActiveCell.Address)
'also tried activecell.cells, etc.- every property that seemed promising
End Sub

Function CustomChanges (xTargetCell as range)
'do stuff
xTargetCell.value = a
xTargetCell.interior.color = b
End Function

(2) In case anyone who reads this is feeling particularly generous, when a
multi-cell range is selected, is there a good way to always return the cell
in the upper left corner, regardless of which cell in the range is currently
selected, or how the range is selected? With my current code, if I click A1
and drag to F5, it returns A1 as expected. But if I click F5 and drag to A1,
it returns F5 and I'd much rather always default to the most upper left cell
in a multi-cell range, if possible.

Thank you!!
Keith

--

Dave Peterson