How to reference an active cell

How to reference an active cell

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

-- 
Tomas Vognar

date: Tue, 11 Mar 2008 12:00:49 +0100 author: Tomá¹ Vognar vognar(zavinac)esys.cz

Re: How to reference an active cell

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

This newsgroup is for the use of VBA with Word.

-- 
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

"Tomá¹ Vognar" <vognar(zavinac)esys.cz> 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 
> 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
>
> -- 
> Tomas Vognar

date: Tue, 11 Mar 2008 21:32:38 +1000 author: Doug Robbins - Word MVP

Re: How to reference an active cell

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.

-- 
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
> 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
>
> -- 
> Tomas Vognar

date: Tue, 11 Mar 2008 12:39:59 +0100 author: Stefan Blom

Re: How to reference an active cell

Hi Tomá¹,

Try this:

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

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

"Tomá¹ Vognar" <vognar(zavinac)esys.cz> 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 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
>
> -- 
> Tomas Vognar

date: Tue, 11 Mar 2008 23:22:17 +1100 author: macropod lid

Re: How to reference an active cell

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


"macropod" <invalid@invalid.invalid> pí¹e v diskusním pøíspìvku 
news:eIDpGK3gIHA.3780@TK2MSFTNGP06.phx.gbl...
> Hi Tomá¹,
>
> Try this:
>
> Sub RowFormatter()
> With ActiveCell.EntireRow.Interior
>    .ColorIndex = 15
>    .Pattern = xlSolid
> End With
> End Sub
>
> Cheers
> -- 
> macropod
> [MVP - Microsoft Word]
> -------------------------
>
> "Tomá¹ Vognar" <vognar(zavinac)esys.cz> 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 
>> 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
>>
>> -- 
>> Tomas Vognar
>

date: Wed, 12 Mar 2008 09:33:23 +0100 author: Tomá¹ Vognar vognar(zavinac)esys.cz

Re: How to reference an active cell

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
-- 
macropod
[MVP - Microsoft Word]
-------------------------

"Tomá¹ Vognar" <vognar(zavinac)esys.cz> wrote in message news:%23sb%23$uBhIHA.3788@TK2MSFTNGP03.phx.gbl...
> 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
>
>
> "macropod" <invalid@invalid.invalid> pí¹e v diskusním pøíspìvku news:eIDpGK3gIHA.3780@TK2MSFTNGP06.phx.gbl...
>> Hi Tomá¹,
>>
>> Try this:
>>
>> Sub RowFormatter()
>> With ActiveCell.EntireRow.Interior
>>    .ColorIndex = 15
>>    .Pattern = xlSolid
>> End With
>> End Sub
>>
>> Cheers
>> -- 
>> macropod
>> [MVP - Microsoft Word]
>> -------------------------
>>
>> "Tomá¹ Vognar" <vognar(zavinac)esys.cz> 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 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
>>>
>>> -- 
>>> Tomas Vognar
>>
>

date: Wed, 12 Mar 2008 23:10:42 +1100 author: macropod lid