APPLY A CELL's FORMAT WITHOUT SELECTING

Any idea how to have a cell(s) for example A1:A10 acquire the exact format as 
of B1 without copy, selecting & pasting special xlPasteFormats?
-- 
Thanx in advance,
Best Regards,

Faraz
0
Utf
12/22/2009 8:03:02 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
780 Views

Similar Articles

[PageSpeed] 10

This macro will do that...

Sub CopyB1sFormat()
  Dim V As Variant, Ra As Range, Rb As Range
  Set Ra = Range("A1:A10")
  Set Rb = Range("B1")
  V = Join(WorksheetFunction.Transpose(Ra.Value), Chr$(1))
  Rb.Copy Ra
  Ra = WorksheetFunction.Transpose(Split(V, Chr$(1)))
End Sub

-- 
Rick (MVP - Excel)


"Faraz A. Qureshi" <FarazAQureshi@discussions.microsoft.com> wrote in 
message news:6207F8C3-6AA2-4189-9850-764335E86EE8@microsoft.com...
> Any idea how to have a cell(s) for example A1:A10 acquire the exact format 
> as
> of B1 without copy, selecting & pasting special xlPasteFormats?
> -- 
> Thanx in advance,
> Best Regards,
>
> Faraz 

0
Rick
12/22/2009 8:38:03 AM
WOW!
XClent!
Thanx a lot a pal!
Really did off-load a burden!

However would u kindly interpret the code?
-- 
Thanx in advance,
Best Regards,

Faraz


"Rick Rothstein" wrote:

> This macro will do that...
> 
> Sub CopyB1sFormat()
>   Dim V As Variant, Ra As Range, Rb As Range
>   Set Ra = Range("A1:A10")
>   Set Rb = Range("B1")
>   V = Join(WorksheetFunction.Transpose(Ra.Value), Chr$(1))
>   Rb.Copy Ra
>   Ra = WorksheetFunction.Transpose(Split(V, Chr$(1)))
> End Sub
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Faraz A. Qureshi" <FarazAQureshi@discussions.microsoft.com> wrote in 
> message news:6207F8C3-6AA2-4189-9850-764335E86EE8@microsoft.com...
> > Any idea how to have a cell(s) for example A1:A10 acquire the exact format 
> > as
> > of B1 without copy, selecting & pasting special xlPasteFormats?
> > -- 
> > Thanx in advance,
> > Best Regards,
> >
> > Faraz 
> 
> .
> 
0
Utf
12/22/2009 9:34:01 AM
Here is the same code I posted along with comments explaining what is going 
on. The main thrust of the code is to protect the original values in the 
cells having their format changed (because the Copy operation will overwrite 
them).

Sub CopyB1sFormat()
  '  Declare variable
  Dim V As Variant, Ra As Range, Rb As Range
  '  Assign the destination range
  Set Ra = Range("A1:A10")
  '  Assign the source cell to use for the formatting
  Set Rb = Range("B1")
  '  Copy existing values from the destination cells into a string...
  '  Transpose takes a contiguous **column** of cells and creates a
  '  one-dimensional array from them which the Join function can then
  '  operate on... the Chr$(1) is just a delimiter character... any
  '  character can be use, but that character should never be able to
  '  appear in the text of any cell being joined (otherwise Split'ting
  '  them apart later will be impossible to do... Chr$(1) is a just
  '  a non-typable character that can't (under normal circumstances)
  '  appear in text string.
  V = Join(WorksheetFunction.Transpose(Ra.Value), Chr$(1))
  '  Copy the contents and formatting from the source cell into the
  '  destination cell
  Rb.Copy Ra
  '  Put the original values that were in the source cells back into
  '  the source cells. Split creates a one-dimensional array from the
  '  text string stored in V and the Transpose function puts it back
  '  into a form that can be assign to a range of cells.
  Ra = WorksheetFunction.Transpose(Split(V, Chr$(1)))
End Sub

-- 
Rick (MVP - Excel)


"Faraz A. Qureshi" <FarazAQureshi@discussions.microsoft.com> wrote in 
message news:912C523D-4C19-4BB7-BC65-07C46F924371@microsoft.com...
> WOW!
> XClent!
> Thanx a lot a pal!
> Really did off-load a burden!
>
> However would u kindly interpret the code?
> -- 
> Thanx in advance,
> Best Regards,
>
> Faraz
>
>
> "Rick Rothstein" wrote:
>
>> This macro will do that...
>>
>> Sub CopyB1sFormat()
>>   Dim V As Variant, Ra As Range, Rb As Range
>>   Set Ra = Range("A1:A10")
>>   Set Rb = Range("B1")
>>   V = Join(WorksheetFunction.Transpose(Ra.Value), Chr$(1))
>>   Rb.Copy Ra
>>   Ra = WorksheetFunction.Transpose(Split(V, Chr$(1)))
>> End Sub
>>
>> -- 
>> Rick (MVP - Excel)
>>
>>
>> "Faraz A. Qureshi" <FarazAQureshi@discussions.microsoft.com> wrote in
>> message news:6207F8C3-6AA2-4189-9850-764335E86EE8@microsoft.com...
>> > Any idea how to have a cell(s) for example A1:A10 acquire the exact 
>> > format
>> > as
>> > of B1 without copy, selecting & pasting special xlPasteFormats?
>> > -- 
>> > Thanx in advance,
>> > Best Regards,
>> >
>> > Faraz
>>
>> .
>> 

0
Rick
12/22/2009 8:54:28 PM
Reply:

Similar Artilces:

Auto Fill Cells
I am building an inpatient log for a nursing home. I need to auto fill the patient's name and address from their ID # Example in column C (ID#) 255 - would automatically fill in Column D (last name), Column E(first name), Column F(middle initial), and Column G(address) At this point - all of the cells are blank. Sounds nice. Where is the data coming from? Take a look at these links for some ideas of how you can go about doing this: http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlFunctions03.html#IndexMatch http://www.contextures.com/xlFunc...

comments in a cell #2
HYCH, Just a question? is there any way of using some vba that will add coments to a cell and have the comment box appear as it does if i add my own comments manually, would like the comment hidden until the cell is selected and then have it show up! obviously i know this happens when you manually process comments, but i would like the comment box to appear and display values that are in another worksheet within the same workbook. hope that makes sense. Steve Steve wrote: > > HYCH, > > Just a question? > > is there any way of using some vba that will add coments t...

shading cells that are not consecutive
Is it possbile to shade cells B10-16 and D14-8 and F2 and etc and forma them as a group? Thank yo -- Message posted from http://www.ExcelForum.com Use Ctrl to select all the rages you want selected then do th formatting you wish. Either that or choose one cell and do the formatting the way you want Then copy it and select the other cells and go to edit: paste special formats -- Message posted from http://www.ExcelForum.com ...

Replacing cell with text
Hi, I want to know if it is possible to do the following: Imagine I have a legend in which I say which text corresponds to whic letters ex: John -> J Harry -> H Susan -> S and what I want to do is when entering J, H or S in a cell, it wil change to the corresponding text. Is there a way of doing it without having to run a macro ? Thanks in advance Pedr ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Go to Tools > AutoCorrect Options (XL2002) and insert new...

Formula for counting specific cells which are greater than 1
I'm looking for a formula that will count specific cells (not a range - A17, A27, A37 and so on) that will have a certain value (above $1.00 to make it easy). So if the cell has a value of $1.00 or less it will not count in the total; If it is above $1.00 if will count. If A17 is above $1.00, A27 is $0.50 & A37 is $3.00 the count will be 2. ...

Outlook 2003 HTML formatting problem
After upgrading clients to Outlook 2003 we've started to experience a strange problem with emails formatted in HTML. The scenario goes like this. 1. A local user creates an HTML formatted email and sends it to an external recipient. 2. The external recipient replies to the original message. 3. Originator replies to the reply 4. The external recipient receives the HTML formatted email with portions of the text grossly enlarged. 5. Upon review, it would appear that the external HTML client dropped a (.) changing the font size from 10.0 to 100 causing the enlarged text. Editing the HT...

Excel Date Format of MMM/YY
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I enter a date in the format of MMM/YY it saves ok but if you then look in tools and calculator I have found that it creates a formula of 2004 and when you copy this over to a Windows based PC it displays incorrect dates. How do I turn off this apparent formula creation. I live in UK and have settings to UK Don't let the info in the Calculator mislead you. It's simply confirming that the workbook you're in is employing the 1904 Date System which is the default in Mac Excel. Dates in Mac Exce...

Changing the 'Mark item as read when selection changes' at runtime
Hi, Outlook version 2007 VS2008 Using VSTO and Redemption I need to change the following settings at runtime Mark items as read when viewed in the Reading Pane Wait [ ] seconds before marking the item as read. Mark item as read when selection changes. All the above settings are on a form found here: Tools --> Options --> Other --> Reading Pane The registry settings are stored in [HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Preferences] I know how to change the registry settings but I don't know to re-load these settings again without ...

Dynamicly change spreadsheet tab names depending on cell value
Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 for each day of the month) These tabs are named - 1,2,3 e.t.c Is it possible to define rule that would ad "!" to day which is weekend day. In other words is it possible to dynamicly change tab name depending from value of cell ? If this is possbile how do i do this ? Right-click on the spreadsheet tab, select View Code and paste this in:- Private Sub Worksheet_Activate() ActiveSheet.Name = Range("A1").Value End Sub To test this I put the date in cell B2 and this formula into A1:- =TEX...

formatted values from single equation
I would really like to have a Excel spreadsheet with two worksheets, the first sheet has raw data (16 across, 100's down) and what I would like to do on the second sheet is add to the first row, 16 equations (lets say for now y=x). then underneath this the formatted data where x is cell A1 on the first worksheet and y is on the second worksheet. Obviously you would just normally in each cell on worksheet 2 type the equation in (='worksheet1'!A1), but I would like to show people the equation being used. Is this possible, does this message make any sense?? Nice -- mr_nice! ---...

Formating in a Report, the highest 3 records
How do i can format the hightes 3 records on an Access Report? Unfortunately the Access doesn't have the Excel have the function "Large"... Can you help me? Look in Access Help for "TOP". HTH -- -Larry- -- "Luis Marques" <Luis Marques@discussions.microsoft.com> wrote in message news:F467B234-EA95-4F79-91B0-EDC12E61FD00@microsoft.com... > How do i can format the hightes 3 records on an Access Report? > > Unfortunately the Access doesn't have the Excel have the function "Large"... > > Can you help me? Try writing your ...

substract cell F from cell H and total into cell I
i am new to using excel. i need to be able to put in pay price in cell F and selling price into cell H and get total in cell i. so it would have to substract cell F from cell H and put total into cell i. how do i do this? currency only Assuming your data is in Row 1, put this formula in I1 and press Enter.........be sure and type it just as shown, as the leading equal sign is what tells Excel that the following characters are a formula. =H1-F1 Vaya con Dios, Chuck, CABGx3 "vadarpug" <vadarpug@discussions.microsoft.com> wrote in message news:617768AC-D261-47AE-861A-E6BFD...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

Change a cell's value using a button
Hi - I know its possible but have no idea how to do it - How do I set up a cell with two buttons attached to it that increase and decrease the value in the cell? I.e. if I have a cell with 10% in it and hit the "up" button, the number goes up by 0.5% and down by the same amount if I hit the "down" button? I am pretty good in Excel but dont know anything about macros - but Im really not sure where you find this function. Thanks for your help in advance anyone! -- Ashley No macros required. Use a spin button control rather than 2 buttons. Follow these steps: 1. Go to...

File Formate
I have five Excel files which contain the records of my previous 4 years Business matters. Suddenly yesterday when I opned one of my files I received the following messege. --------------------------------------- The file is not in recognizable formate. if you know the file is from another program which is incompatible with Microsoft Excel, click Cancel, then open the file in its original application. If you want to open the file later in Microsoft Excel, save it in a format that is compatible, such as text format. If you suspect the file is damaged, click help for more information about...

how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet.
how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet. In VBA Editor, create a module. Paste this there Option Explicit Dim X As New EventClass Public Sub Auto_Open() Set X.App = Application End Sub Then, insert a Class Module, rename it to EventClass, and paste this there: Option Explicit Public WithEvents App As Application Private Sub App_SheetActivate(ByVal Sh As Object) Sheets(Sh.Name).Select ActiveCell.SpecialCells(xlLastCell).Select End Sub It should work (XL2000). "Daniel" <...

Question about toggle selections?
First off hi... I was wondering if there is a way to have a toggle drop box to list th names of all sheets listed in my workbook. e.g i want all the sheet names listed in the box so that what ever wa entered in the next cell, to be able to research what was selected i the first cell. i want the user to select the option by a drop down button. TI -- Message posted from http://www.ExcelForum.com In the absence of other replies ... the context/meaning of your questio is not clear. Presumably you mean a Combobox/Dropdown but do not indicate whether i a worksheet or user form. Whatever ...

Calculate Formulas in Highlighted Cells Only--not whole sheet
In office 97 I was able to recalc only the cells highlighted (control + L) as opposed to the entire sheet or workbook. In Excel 2002, how do I recalc only the cells I have highlighted? Thanks I don't recall this shortcut in xl (any version). I'm guessing that you had a macro that did something special. In xl2002, you could use a macro like this (assigned to ctrl-shift-l) Option Explicit Sub testme() Selection.Calculate End Sub But I think I've read posts that calculating a single range can be bad--it can screw up the calculation dependencies. I think that Charles Will...

Mouse select problem
I keep running into this problem from time to time. My mouse will act as if the shift key is pressed and selects everything between the current cursor position and the click. This manifests in VBA, in fields on the Property window and in query designer. If I restart Access the problem goes away. Sticky keys are off. Access 2003, XP Pro If I remember correctly this also happened in previous versions of Access This happens when you keep the shift key pressed for too long at a time. There is a solution to undo it that I forgot. You could search for it. My solution -- get out of the ...

Dynamic Range Defined by Value of Cell
I am just getting started with dynamic ranges. I have data as follows: A B C Product1 qty cost Product2 qty cost .... ProductN qty cost TOTAL qtytot costtot The number of products varies. There is other data below this that is unrelated. How can I define a range dynamically that will always capture A:C and as many rows up and including the first time it finds "TOTAL" in column A? Any help greatly appreciated. Thank you. I don't know what you mean by "define a range" b...

Different formats within the same cell
Hello, i want to enter a word in a cell but only have one of the letter fomatted bold, eg - "wo*r*d" Is this possible -- Message posted from http://www.ExcelForum.com Hi, Yes. Enter your word into the cell, go to the formulae bar, highlight th letter you want to have in bold and then click on the bold icon. This will format just that letter to bold -- Message posted from http://www.ExcelForum.com sure. Just edit the word>highlight the letter(s) and change the font,bold,color etc. will NOT work withing a formula. -- Don Guillett SalesAid Software donaldb@281.com "...

Number formats and fields
Hi All! I have a list of equations in a word document that I need to add a reference (the first equation in the document will be equation A, the second will be equation B, etc). So I need a numbering field. However, when I get to equation 28 I want the format to be AB, then 29 to be AC, not BB, CC like the default A, B, C… numbering gives you. However, I will be adding other equations randomly throughout the document and referring to the equation reference in text paragraphs, so I would like them to auto update when I add another equation in the middle of the document. Doe...

Update Cell Value Based on different Cell
Hi, new to the forum but I've been reading it for a couple of days now. Looking forward to talking and learning with everyone. I think I a better than average with Excel but this problem is driving me nuts! have a list of lottery numbers listed by date drawn (see below fo example) on one worksheet. On another sheet I have a count of how man times a number has been drawn. I would like to add a column to show th last date a number was drawn and have it update automatically when I ad a new drawing. However, I haven't been able to figure out how to ge the date to update automaticall...

Formatting a date in client report
How do I format a date field in a client side Microsoft report (rdlc)? I'm using VS2008, 9.0.30729.1 sp Windows forms project. Unformatted, the dates in my report are displayed like this: 11/19/09 00:00:00 I want to display just the date, not the zeroes. If I enter an expression in the value property for the report textbox like this: =Format(Fields!StartDate.Value, "D") (as shown in http://msdn.microsoft.com/en-us/library/ms251668.aspx) the report will display "D" instead of the formatted date. The same thing happens with other format string...

Conditional Formatting #84
I would like to use conditional formatting in a number of cells. The condition I would like to highlight would be if the cell contained text. ie. 1Y Can anyone help me with this as I am fairly new... Thanks!! Mike 1. Select your range. 2. Go to Format > Conditional Formatting. 3. Select "Formula Is" and insert the following: =COUNTIF(INDIRECT("rc",0),"*1Y*") 4. Press the Format radio button and format as desired. HTH Jason Atlanta, GA >-----Original Message----- >I would like to use conditional formatting in a number of >cells. The co...