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, beginner, Office XP 

0
iso
3/11/2008 11:19:09 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
548 Views

Similar Articles

[PageSpeed] 36

Sub Za�ed�n�_��dku()
    With Activecell.Entirerow.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
End Sub


-- 
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tom� Vognar" <vognar(zavinac)esys.cz> wrote in message 
news:ec1j7m2gIHA.4436@TK2MSFTNGP02.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, beginner, Office XP 


0
bob.NGs1 (1661)
3/11/2008 11:42:22 AM
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


"Bob Phillips" <bob.NGs@somewhere.com> p�e v diskusn�m p��sp�vku 
news:uDiT7z2gIHA.4712@TK2MSFTNGP04.phx.gbl...
> Sub Za�ed�n�_��dku()
>    With Activecell.Entirerow.Interior
>        .ColorIndex = 15
>        .Pattern = xlSolid
>    End With
> End Sub
>
>
> -- 
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my 
> addy)
>
>
>
> "Tom� Vognar" <vognar(zavinac)esys.cz> wrote in message 
> news:ec1j7m2gIHA.4436@TK2MSFTNGP02.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, beginner, Office XP
>
> 

0
iso
3/12/2008 8:34:16 AM
I am not understanding. Ay to Wy, what is that referring to?

-- 
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tom� Vognar" <vognar(zavinac)esys.cz> wrote in message 
news:e98EgvBhIHA.1188@TK2MSFTNGP04.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
>
>
> "Bob Phillips" <bob.NGs@somewhere.com> p�e v diskusn�m p��sp�vku 
> news:uDiT7z2gIHA.4712@TK2MSFTNGP04.phx.gbl...
>> Sub Za�ed�n�_��dku()
>>    With Activecell.Entirerow.Interior
>>        .ColorIndex = 15
>>        .Pattern = xlSolid
>>    End With
>> End Sub
>>
>>
>> -- 
>> ---
>> HTH
>>
>> Bob
>>
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my 
>> addy)
>>
>>
>>
>> "Tom� Vognar" <vognar(zavinac)esys.cz> wrote in message 
>> news:ec1j7m2gIHA.4436@TK2MSFTNGP02.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, beginner, Office XP
>>
>>
> 


0
bob.NGs1 (1661)
3/12/2008 10:59:48 PM
The best would be region defined by intersection of the CurrentRegion and 
active line. I'm sorry, I'm trying this, but I get errors or wrong results. 
I do not quite understand the syntax of the VBA, for the meantime.

T.Vognar


"Bob Phillips" <bob.NGs@somewhere.com> p�e v diskusn�m p��sp�vku 
news:uXF5KTJhIHA.5280@TK2MSFTNGP04.phx.gbl...
>I am not understanding. Ay to Wy, what is that referring to?
>
> -- 
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my 
> addy)
>
>
>
> "Tom� Vognar" <vognar(zavinac)esys.cz> wrote in message 
> news:e98EgvBhIHA.1188@TK2MSFTNGP04.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
>>
>>
>> "Bob Phillips" <bob.NGs@somewhere.com> p�e v diskusn�m p��sp�vku 
>> news:uDiT7z2gIHA.4712@TK2MSFTNGP04.phx.gbl...
>>> Sub Za�ed�n�_��dku()
>>>    With Activecell.Entirerow.Interior
>>>        .ColorIndex = 15
>>>        .Pattern = xlSolid
>>>    End With
>>> End Sub
>>>
>>>
>>> -- 
>>> ---
>>> HTH
>>>
>>> Bob
>>>
>>>
>>> (there's no email, no snail mail, but somewhere should be gmail in my 
>>> addy)
>>>
>>>
>>>
>>> "Tom� Vognar" <vognar(zavinac)esys.cz> wrote in message 
>>> news:ec1j7m2gIHA.4436@TK2MSFTNGP02.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, beginner, Office XP
>>>
>>>
>>
>
> 

0
iso
3/14/2008 8:24:44 AM
Sub Za=B9ed=ECn=ED_=F8=E1dku()
    With Range("A12:W12").Interior
        .ColorIndex =3D 15
        .Pattern =3D xlSolid
    End With
End Sub


--JP

On Mar 14, 4:24=A0am, Tom=E1=9A Vognar <vognar(zavinac)esys.cz> wrote:
> The best would be region defined by intersection of the CurrentRegion and
> active line. I'm sorry, I'm trying this, but I get errors or wrong results=
..
> I do not quite understand the syntax of the VBA, for the meantime.
>
> T.Vognar
>
0
jp2112 (204)
3/14/2008 8:56:44 PM
Reply:

Similar Artilces:

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

00/01/1900 In the date cell when linked cell is empty?
Hi I have 52 worksheets all linked up to a total sheet that is working very well thanks to RD All I need to do now is sort out the date this is copied from sheet1 onto the totals page and works fine when the date is put into sheet1. However if the cell is blank on sheet2 the date is put into the totals sheet as 00/01/1900 when the date is put in sheet2 all is well with the right date entered into the totals sheet. So how do I get the totals date to stay blank until the weekly sheets have a date put into the sheet please. the format I am using is below many thanks for all the help I ...

Active-x #2
Hi, I am writing an MFC UI active x control! The control work fine when it was in a shape of dialog wit buttons, but then I change the control UI to be in the form of tab control so I use the tab control but when I put this control in a container application it cause the application to stack when I press the OK button of the tab control I change the tab control inside my active-x to property sheet but I get the same problem! why??? Can I use property sheet control or tab control inside my MFC active x control or I cant use those controls??? Thanks ...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

check or uncheck a check box based on a cell value
IN EXCEL, I WANT TO HAVE A CHECK BOX CHECKED IF A DOLLAR AMOUNT IS ENTERED IN A CELL AND UNCHECKED IF THE CELL IS ZERO 1. Please, do not use all capitals, it's as if you are shouting! 2. If the cell with the $ amount is A1 then enter =A1<>0 in B1 (or any other empty cell) and give B1 as linked cell of the checkbox! -- Regards! Stefi „RTKCPA” ezt írta: > IN EXCEL, I WANT TO HAVE A CHECK BOX CHECKED IF A DOLLAR AMOUNT IS ENTERED IN > A CELL AND UNCHECKED IF THE CELL IS ZERO ...

Outlook 2000 Contact activities missing
Help! I often use the Contact Activities tab in Outlook 2000, to look up last contact, notes, etc. It has always taken awhile to find the items, but eventually (within a minute or so) they start to show up. Spontaneously (AFAIK) about a month ago, when I click on the Activities tab, the search icon starts up but never finds any entries. This is on contacts where I can quickly see journal entries, so I know it should find them. I have let it run over lunch and overnight, and it never finds anything - just searches forever. This seems to have happened at the same time that Outlook ma...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

How to move cells to a another Cell?
Hello, I hope someone can help me on this. I am trying to move cells from a group into a top cell, the initial view of column A (very simplistic) of the cell looks like this: 1 2 3 4 1 2 2 2 3 3 3 1 1 2 3 What I want to do is to move 2 3 4 from the first group to the cell that contains the 1 (first entry) and continue with the rest of the cells so at the end columnA will look like this: 1 2 3 4 1 2 2 2 333 1 123 The cells vary in lenght and I have several spreasheets with LARGE amounts of data that I have to processed, so I am hoping that there is a macro (VB) that is able to do th...

SFO Activity not appear after new Business Unit
I recently changed the Business Unit that a user was a member of. This user already owned a number of records before changing the Business Unit he was a member of. When I went offline, none of these records synced to Outlook SFO. I determined that if I reassigned these accounts to the user, they began to sync to properly. However, it does not appear that Activities are syncing. When online the activities appear on the proper contact record and in the users activity list. When offline, the activity does not sync - it is not on the contact record nor listed as an open activity. I tried as...

protecting a group of cells not a whole worksheet
I would like to protect a group of cells and not a whole worksheet but I haven't been able to figure out how to do this. I think this would be the best way for what I am wanting to do. I have a spreadsheet that I send to 13 people. They make changes and send back to me and then I have to up-date the changes. If I could protect all the cells except for the one they enter in - then I could save the file on a shared drive and it would eliminate me up-dating. Is there a way to do this? Hi select the cells for which you want to allow entries. After this goto 'Format - cells - Protection&...

Image in a cell
Is it possible to place an image in a cell? If so, can you tell me how to do it? No. Images are placed in the Drawing Layer "above" the worksheet. Cells can contain formulae or data. If you size an image to exactly cover a cell, you can set the Move and Size with Cells property (right-click on the image, choose Format Picture/Properties and select the appropriate radio button) so that the image remains "on top" of the cell. In article <6463B5FA-3D07-43B9-90EE-7A5143977C1B@microsoft.com>, "Mark" <Mark@discussions.microsoft.com> wrote: > Is ...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

how do I merge two adjacent cells and keep the data from both cell
To take it a step further, I also need the formats to remain the same. ie...the left column data is Left-text-justified, and the right column is Right-text-justified. =A1 & " " & B1 -- Regards Dave Hawley www.ozgrid.com "johncaleb" <johncaleb@discussions.microsoft.com> wrote in message news:E182C58A-A577-43ED-9C90-46E58767E7CB@microsoft.com... > To take it a step further, I also need the formats to remain the same. > ie...the left column data is Left-text-justified, and the right column is > Right-text-justified. ...

Conditional Formatting on multiple cells
Can a conditional format be applied, ie display a colour, to a range of cells based on a conditon in one of the cells contained in the range? Hi Karyn, If everything is dependent on a single cell, or if everything on a row is dependent on a cell in the row there is no problem. Past that how about an example. See : http://www.mvps.org/dmcritchie/excel/condfmt.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Kar...

Array activation question
Morning all. I'm making a user form to simplify the input of data for a complicated worksheet function. One of the things I was told about this function is that it requires a specific key combination-- ctrl+shift+enter, with the cell being active (the cursor needs to be active within the cell field, or the formula bar). Apparently this places two {} on the outsides of the equation, to do something to it that makes it work. And it's not enough to just place the {} on the equation manually. It specifically requires the keystroke combination mentioned above. My questio...

more than 4 color choses for cell/colum
I an new to excess so please show me how this is to be done I would like to put a letter in a cell but all in the same colum. "A" and get a color ( dont matter what color ) "B" and get a color ( dont matter what color ) "C" and get a color ( dont matter what color ) "D" and get a color ( dont matter what color ) "E" and get a color ( dont matter what color ) thanks jladika@millenniumimagingfl.com jo -- Message posted from http://www.ExcelForum.com Hi only possible with VBA as conditional formating only supports 3 conditi...

Data Validation in cell
Hi I have a sales order form with the equipment on the second sheet as "PartNo" "Description" "Cost" selected each column of data then clicked in the cellname box and called them "PartNo" "Description" "Cost" On the sales order form I have the columns "Qty" PartNo" "Description" "Serial No" "QtyDes" "Price" Total" I have set up data validation for the description as a list with =Description what I want it to do is display the part no in the part number Column and the cost in...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

deleting duplicated cells in a list via advanced autofilter?
I have a list which I would like to filter and deleted duplicate records. A1: A250= account numbers B1:""= values C1:"" = loan balances D1:"" = notes I'd like to be able to delete the duplicate account numbers but stil show the value, loan balances, and notes that belong to the accounts. Can anyone help. Thanks. Pete: -- petevan ----------------------------------------------------------------------- petevang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2503 View this thread: http://www.excelforum.com/showthread.php?threadi...

Empty Cells #3
I export data from a program called OpenView Service Desk into a spreadsheet. This spreadsheet is then imported into Access and queries performed on the imported data. This data is then copied and pasted into a different spreadsheet where calculations are performed. In one particular spreadsheet there are 2 columns, one complete with data (no empty cells) and the other which may contain empty cells. I need to count the number of "empty cells" however the problem is, these cells, although they appear empty, are not. I need to but cannot find the content(s) of this cell(s). How do I fi...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

How do Macro to EDIT cells?
Am using MS Excel XP (2002). All I want to do is a short macro to, in effect, press F2 (to EDIT the cell), then let's say delete the first character of the cell and change it to let's say X, like this... Before: _Mary Jones *Joe Smith %Peter Rabbbit After: XMary Jones XJoe Smith XPeter Rabbbit I just want to put the cursor on a given cell, click CNTL-A to run the macro, and have it change one cell at a time. There are reasons why I can't use the search and replace. Problem is... if I record the macro (turn it on, press the key sequences, F2, Home, Delete, X, GoDownOneCell, ...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Date Formats for Cells Do not match System Date Formats
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have just loaded Office 2008 and then update to the latest update 12.2.4 <br> I have my system preferences set to Australia for date and time using the date format dd/mm/yyyy. But in Excel when I open the format cell dialogue and select date the only formats available for selection are in the mm/dd/yyyy formats. When I checked in &quot;Numbers&quot; the dd/mm/yyyy format is used so &quot;Numbers&quot; is picking up the system preference why not the excel. Did you by any chance have ...

active label on smartlist
In the Account Maintenance window, there is a checkbox to mark an Account as inactive which is labeled "Inactive". However, in the SmartList view for Accounts, there is a column you can add called "Active". It would make more sense that these labels are the same so it's not confusing to the end user. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open th...