Find address of active cell
 

Is there a function that returns the row, column or address of the active cell?
I'd like to build a formula in a column that displays a character if the
active cell is in that row, such as:
=IF(ROW()=ROW(ActiveCell),"X")
Thanks.

Find address of active cell
 

Use the CELL() function, CELL("row") will always return the row of the active
cell:

=IF(ROW()=CELL("row"),"X")

Note this will continue to populate if the active cell is in the same row on
another tab as well.
--
Regards,
Dave


"Dave" wrote:

Is there a function that returns the row, column or address of the active cell?
I'd like to build a formula in a column that displays a character if the
active cell is in that row, such as:
=IF(ROW()=ROW(ActiveCell),"X")
Thanks.

Find address of active cell
 

Thanks for the the suggestion but the formula seems to always return true
even when the active cell is no longer in the row with the fromula.
I need to compare the row that contains the formula with the row of the
active cell.
If they are the same then display "X" else display nothing.


"David Billigmeier" wrote:

Use the CELL() function, CELL("row") will always return the row of the active
cell:

=IF(ROW()=CELL("row"),"X")

Note this will continue to populate if the active cell is in the same row on
another tab as well.
--
Regards,
Dave


"Dave" wrote:

Is there a function that returns the row, column or address of the active cell?
I'd like to build a formula in a column that displays a character if the
active cell is in that row, such as:
=IF(ROW()=ROW(ActiveCell),"X")
Thanks.

Find address of active cell
 

It works fine for me. Try hitting F9... does that update your formula? If
so you have automatic calculation turned off, it's gotta be turned on for the
formula to work. To turn on:

<Tools
<Options
"Calculation" tab
Check "Automatic"

Post back if that still doesn't fix it.

Also, if you want the formula to display nothing when the active cell
doesn't equal the row the formula is in, then gotta add a condition to the IF
formula:

=IF(ROW()=CELL("row"),"X","")

--
Regards,
Dave


"Dave" wrote:

Thanks for the the suggestion but the formula seems to always return true
even when the active cell is no longer in the row with the fromula.
I need to compare the row that contains the formula with the row of the
active cell.
If they are the same then display "X" else display nothing.


"David Billigmeier" wrote:

Use the CELL() function, CELL("row") will always return the row of the active
cell:

=IF(ROW()=CELL("row"),"X")

Note this will continue to populate if the active cell is in the same row on
another tab as well.
--
Regards,
Dave


"Dave" wrote:

Is there a function that returns the row, column or address of the active cell?
I'd like to build a formula in a column that displays a character if the
active cell is in that row, such as:
=IF(ROW()=ROW(ActiveCell),"X")
Thanks.

Find address of active cell
 

Billigmeier's formula works very well, but needs the F9 to refresh. if you
want to save wear and tear on F9, consider the following worksheet code:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 5 Then
Cells(5, 1).Value = "X"
Else
Cells(5, 1).Value = ""
End If
End Sub

It will set/clear A5 depending upon the location of the selected code.
--
Gary''s Student


"Dave" wrote:

Thanks for the the suggestion but the formula seems to always return true
even when the active cell is no longer in the row with the fromula.
I need to compare the row that contains the formula with the row of the
active cell.
If they are the same then display "X" else display nothing.


"David Billigmeier" wrote:

Use the CELL() function, CELL("row") will always return the row of the active
cell:

=IF(ROW()=CELL("row"),"X")

Note this will continue to populate if the active cell is in the same row on
another tab as well.
--
Regards,
Dave


"Dave" wrote:

Is there a function that returns the row, column or address of the active cell?
I'd like to build a formula in a column that displays a character if the
active cell is in that row, such as:
=IF(ROW()=ROW(ActiveCell),"X")
Thanks.

Find address of active cell
 

Thanks very much, guys

I used a combination of the solutions.
Using the VBA code to change a value in a range forces a calculation and
allows the formulas to refresh.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("CurRow") = ActiveCell.Row
End Sub

=IF(ROW()=CELL("row"),"X","")




"Gary''s Student" wrote:

Billigmeier's formula works very well, but needs the F9 to refresh. if you
want to save wear and tear on F9, consider the following worksheet code:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 5 Then
Cells(5, 1).Value = "X"
Else
Cells(5, 1).Value = ""
End If
End Sub

It will set/clear A5 depending upon the location of the selected code.
--
Gary''s Student


"Dave" wrote:

Thanks for the the suggestion but the formula seems to always return true
even when the active cell is no longer in the row with the fromula.
I need to compare the row that contains the formula with the row of the
active cell.
If they are the same then display "X" else display nothing.


"David Billigmeier" wrote:

Use the CELL() function, CELL("row") will always return the row of the active
cell:

=IF(ROW()=CELL("row"),"X","")

Note this will continue to populate if the active cell is in the same row on
another tab as well.
--
Regards,
Dave


"Dave" wrote:

Is there a function that returns the row, column or address of the active cell?
I'd like to build a formula in a column that displays a character if the
active cell is in that row, such as:
=IF(ROW()=ROW(ActiveCell),"X")
Thanks.