Prompt for new Active Cell

[drewbsking]

Is there a way for VBA to prompt the user for a new selection?.

For exmaple,

Sub BlankRow()

The reason I need this is because I am already in a macro and can not change selection on screen and I don't want to exit routine to make new selection (last line of code). 

'I need to be able to select new active cell!

ActiveCell.EntireRow.Select
Selection.ClearContents
ActiveCell.EntireRow.Interior.ColorIndex = 1
ActiveCell.Offset(1, 0).Select

Edit.Show
End Sub

 

[VoG]

Maybe like this

Dim r As Range
'I need to be able to select new active cell!

ActiveCell.EntireRow.ClearContents
ActiveCell.EntireRow.Interior.ColorIndex = 1
Set r = Application.InputBox("Select a cell", Type:=8)
r.Select

 

[drewbsking]

ah ha! That's what I needed.

Now that I see it I should have known how to use that because I have used the "Set r = Application.InputBox("Select a cell", Type:=8)" elsewhere in my project but used the r in a different way.

Thanks for your help!