How to reference an active cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tomáš Vognar - 11 Mar 2008 12:00 GMT

I've recorded a simple macro for Excel, where I work with a row. But I want
to work with a row the active cell is on, but Macro Recorder has writen me
an absolute referencing. Should anyone help me, how to rewrite the code?

Sub Zašedění_řádku()
Range("A12:W12").Select !!!
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End Sub

Signature

Tomas Vognar

Reply to this Message

Doug Robbins - Word MVP - 11 Mar 2008 12:32 GMT

Better to post to microsoft.public.excel.programming.

This newsgroup is for the use of VBA with Word.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> I've recorded a simple macro for Excel, where I work with a row. But I
> want to work with a row the active cell is on, but Macro Recorder has
[quoted text clipped - 8 lines]
> End With
> End Sub

Reply to this Message

Stefan Blom - 11 Mar 2008 12:39 GMT

I'm not very familiar with Excel VBA, but this seems to work:

Sub TestingRowFormatting()
Dim r As Range
Dim CurrentRow As Long

Set r = ActiveCell

CurrentRow = r.Row

With ActiveSheet.Rows(CurrentRow).Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

End Sub

Note that you will get more knowledgeable help in an Excel newsgroup.

Signature

Stefan Blom
Microsoft Word MVP

"Tomá¹ Vognar" wrote in message
news:efuAsc2gIHA.5280@TK2MSFTNGP04.phx.gbl...

> I've recorded a simple macro for Excel, where I work with a row. But I
> want to work with a row the active cell is on, but Macro Recorder has
[quoted text clipped - 8 lines]
> End With
> End Sub

Reply to this Message

macropod - 11 Mar 2008 13:22 GMT

Hi Tomáš,

Try this:

Sub RowFormatter()
With ActiveCell.EntireRow.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End Sub

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

> I've recorded a simple macro for Excel, where I work with a row. But I want to work with a row the active cell is on, but Macro
> Recorder has writen me an absolute referencing. Should anyone help me, how to rewrite the code?
[quoted text clipped - 6 lines]
> End With
> End Sub

Reply to this Message

Tomáš Vognar - 12 Mar 2008 09:33 GMT

Thank you very much, it works. But is it possible to rewrite it, so that
limited row was marked? (Since Ay to Wy, or using CurrentRegion)

T.Vognar

> Hi Tomáš,
>
[quoted text clipped - 20 lines]
>> End With
>> End Sub

Reply to this Message

macropod - 12 Mar 2008 13:10 GMT

Hi Tomáš,

If you want to colour all of the selected cells (even if they span more than one row):
Sub RowFormatter()
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End Sub

Alternatively, the following modification tells the macro to start in column 1 and stop at column 2 on the current row. You can
change the 1 & 2 to whatever you prefer.
Sub RowFormatter()
With Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 2)).Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End Sub

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

> Thank you very much, it works. But is it possible to rewrite it, so that limited row was marked? (Since Ay to Wy, or using
> CurrentRegion)
[quoted text clipped - 23 lines]
>>> End With
>>> End Sub

Reply to this Message

Rate this thread: