To pass the range to the function:

Sub CustomPicker()

Dim myRange as Range

ActiveCell.Resize(1, 1).Select
Set myRange = ActiveCell
CustomChanges myRange

End Sub


To get the address of the top- and left-most cell in your selection

myAddress = cells(selection.row, selection.column).address




"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