How do I transfer color formats from one work sheet to another

SheetA contain a number  of cells formatted in red and a number in blue (most 
are black).   I wish to transfer this color format to SheetB, but offsetting 
the cells by two columns.     Thus if cell A3 is red in SheetA, I want cell 
C3 to be red in SheetB, and if it is blue I want the color in SheetB to be 
blue.   And I want this for the whole spreadsheet.

Any help with a macro?

Thanks 
0
Kanga85 (12)
11/8/2004 12:53:03 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
450 Views

Similar Articles

[PageSpeed] 48

You could select the region from which you want to transfer and then click 
on the "Format Painter" button on the standard toolbar.  Then on the second 
sheet, select the column offset 2 from original and it should paste all of 
those formats.  (Or use Copy and then Paste Special | Formats).  If you 
wanted the macro for that you could just record that operation.

-Erik

"Kanga 85" <Kanga85@discussions.microsoft.com> wrote in message 
news:E94347E4-9C1F-4194-B9A5-BC0D4144FA7B@microsoft.com...
> SheetA contain a number  of cells formatted in red and a number in blue 
> (most
> are black).   I wish to transfer this color format to SheetB, but 
> offsetting
> the cells by two columns.     Thus if cell A3 is red in SheetA, I want 
> cell
> C3 to be red in SheetB, and if it is blue I want the color in SheetB to be
> blue.   And I want this for the whole spreadsheet.
>
> Any help with a macro?
>
> Thanks 


0
11/8/2004 4:07:47 AM

"E Oveson" wrote:

> You could select the region from which you want to transfer and then click 
> on the "Format Painter" button on the standard toolbar.  Then on the second 
> sheet, select the column offset 2 from original and it should paste all of 
> those formats.  (Or use Copy and then Paste Special | Formats).  If you 
> wanted the macro for that you could just record that operation.
> 
> -Erik
> 
> "Kanga 85" <Kanga85@discussions.microsoft.com> wrote in message 
> news:E94347E4-9C1F-4194-B9A5-BC0D4144FA7B@microsoft.com...
> > SheetA contain a number  of cells formatted in red and a number in blue 
> > (most
> > are black).   I wish to transfer this color format to SheetB, but 
> > offsetting
> > the cells by two columns.     Thus if cell A3 is red in SheetA, I want 
> > cell
> > C3 to be red in SheetB, and if it is blue I want the color in SheetB to be
> > blue.   And I want this for the whole spreadsheet.
> >
> > Any help with a macro?
> >
> > Thanks 
> 
> 
 Thanks Erik, but I just want to PasteSpecial the cell color, not its width  
or its border formats.

0
Kanga85 (12)
11/8/2004 6:49:08 AM
Solved my own problem! (Always the best way?)   This sub is a bit crude as I 
assume I will have no more than 100 rows, and only want to transfer color 
format (and no other cell format details)  from Row B in SheetA to Row F in 
SheetB 

Sub CopyColor()
'
' Copycolor Macro
' Macro recorded 16/11/2004 by Kanga85
'
' Keyboard Shortcut: Ctrl+c
'
   Dim ICI(100) As Integer
   Dim FCI(100) As Integer
   Dim i As Integer
   Sheets("SheetA").Select
   Range("B1").Select
   For i = 1 To 100
     ICI(i) = ActiveCell.Interior.ColorIndex
     FCI(i) = ActiveCell.Font.ColorIndex
     ActiveCell.Offset(1, 0).Select
  Next i
  Sheets("SheetB").Select
  Range("F1").Select
  For i = 1 To 100
    ActiveCell.Interior.ColorIndex = ICI(i)
    ActiveCell.Font.ColorIndex = FCI(i)
    ActiveCell.Offset(1, 0).Select
  Next i
End Sub

"Kanga 85" wrote:

> SheetA contain a number  of cells formatted in red and a number in blue (most 
> are black).   I wish to transfer this color format to SheetB, but offsetting 
> the cells by two columns.     Thus if cell A3 is red in SheetA, I want cell 
> C3 to be red in SheetB, and if it is blue I want the color in SheetB to be 
> blue.   And I want this for the whole spreadsheet.
> 
> Any help with a macro?
> 
> Thanks 
0
Kanga85 (12)
11/16/2004 4:53:01 AM
Reply:

Similar Artilces:

Tab color change on specific value
In Excel 2007, I have a workbook with several sheets. On some of the sheets, there are timer cells i.e. columns of cells with start, duration, end (start + duration), and time_until_done (end - now). The time_until_done is conditionally formatted to highlight any that are completed, but this presumes that I have to go to each sheet and do a manual recalculation (if no automatic recalculations have been done). So I thought it would be nice if the tabs could be highlighted if they need attention. So I put the following in the code for each sheet about which I want to be notifi...

No Color Palette, ActiveX
I have placed a CommandButton on a worksheet from the "Control ToolBox" tool bar. When I right click on the CommandButtom and select "Properties" and get the properties Dialog box to appear. I next select "BackColor" , so I can change the background color of the CommandButton. When I click on the down arrow in "BackColor" property a small dialog box opens with two tabs. When I select "Palette" tab I get a blank Tab. I should have 56 colors to choose from, but they don't appear. Does anyone know what could be causing this problem....

Outlook 2007 Calendar Reminder Not Working
Though I have it selected to remind, the Reminder in Outlook does not work (a reminder pop-up doesn't appear). Any ideas?? W7U 32 Office Pro 2007 Check out this link http://support.microsoft.com/kb/286166 Saidas wrote: >Though I have it selected to remind, the Reminder in Outlook does not work (a >reminder pop-up doesn't appear). > >Any ideas?? > >W7U 32 Office Pro 2007 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/outlook-calendar/201001/1 ...

about transfer order
I created a transfer order "TO666", transfer items from store01 to store02, the system automaticly created a TO No."00001_TO666 ". And the strange thing happend: the items in "TO666" is so different with the items in "00001_TO666 ". where is the problem? with a lot of thanks. Try creating another order with the same items, if the problem still exists, most probably you have a problem in the IDs of those items. What HQ does is to map the items in the created order in Store1 to the corresponding items in HQ, then map them again to Store2 and th...

Is there a non-MFC color picker?
Hi, Is there a non-MFC way to present the user with a color wheel and let them pick a color? Or even if they could choose between a set of 16 or 64 colors, that would help, so long as it is not using MFC and it already a part of Windows. Thanks. Take a look at the ChooseColor sdk function. http://msdn.microsoft.com/en-us/library/ms646912.aspx AliR. "Flark" <flarkino@yahoo.com> wrote in message news:bc720d5a-6d72-4aa6-8d12-70010d204e12@k7g2000hsd.googlegroups.com... > Hi, > > Is there a non-MFC way to present the user with a color wheel > and let them pick ...

Date format #16
I want to format a column of cells so that when 012208 is entered with the numeric keypad, 01/22/2008 appears in the cell. So far, I'm getting 6/3/1933. Thanks, Cathy Hate to say it but why not just type the extra two characters to make it 1/22/08? If you type "12208" in a cell, Excel assumes you are entering a serial date (hence the conversion to 6/3/1933). How much easier to just type the two "/" and don't force Excel to guess what you want. HTH, JP On Jan 22, 10:00=A0pm, "Cathy C" <cchrist...@aristotle.net> wrote: > I want to format a...

Transfering messages
I want to transfer all my messages from one PC running Vista to another running Win7 which I have Windows Mail running on. However, I have two problems: 1. On transferring some of the transferred folders contain the full message, some only contain what you might call place holders e.g my transferred Inbox has 'From' with a list of 30 or so messages but just shows an envelop and the date and time but no message 2. Is there away to transfer attachments too? -- Ron "Ron O'Brien" <castcall@ntlworld.com> wrote in message news:uBH3g6UjKHA.218...

HR Position Transfers
Is there a way to import a mass amount of position changes in GP HR? We are getting ready to effect changes for our upcoming fiscal year. There are numerous changes to our departments, positions moving from one department to another and some of the departments are altogether new. Is there a tool to import Position Change/Transfers? If not, does anyone know all the tables I would need to hit if I need to import the data? ...

does the /cleanprofile command line switch work with Outlook 2010?
I deleted the registry entries for Outlook without backing up my registry (as I should have), and I am now looking for a way to restore them. From my research, it looks like the "/cleanprofile" command line switch would do the trick. However, when I try it with Outlook 2010, I get an error message saying the argument is not valid and to verify it. Am i using the argument with improper syntax? Is it still available with this version of Outlook? If it is no longer supported with 2010, is there a replacement that does the same thing? Am I going down the wrong path ...

Passing RetrieveGlobals9's Connection Object from IM to another dl
I am using RetrieveGlobals9 to get a connection and successfully connecting to the database from within script events in Integration Manager. However, when I tried to pass the open connection to another (VB.Net) application from within the script, I get an error, "Class does not support automation." Is this something that I can fix by rewriting the VB.Net application, or will that always happen when I try to call an external .dll from within Integration Manager? Thanks in advance. ...

Excel to PDG Format Error
Hi, I am used to using Excel 2007 to convert my workbooks into PDF's. However, recently, all of a sudden, everytime I PDF a workbook my Formating and graphs are all wrong. I have noticed that this only happens when I do multiple sheets, but the problem with doing individual ones is that I can't combine them with reader. Try Dr. Dan's All-Purpose Excel-PDF Cure: Select all worksheets by <ctrl> or <shift> clicking from the menu bar: File > Page Setup click into the Page Quality box, and select 600dpi, if it already says 600dpi, select it again. ...

Windows Easy Transfer
I am having trouble with Easy Transfer from an XPhome to XPhome installation. Actually it is the same computer after re-installing XP. I ran the Easy Transfer to back up the data onto an external drive. When I run it now on the new build I don't get the "Continue transfer in Progress" option. It does not ask if this is the new or old computer. I haven't used it before so maybe I am not using it as intended. I have user the XP File settings and transfer program for a situation just like this with no problems. Any suggestions? Hu Computer is a basic HP Pavi...

Default Value + Date Format + Check Box
I could use some help... I have a workorder form. On the form, I have a "FieldRepair" check box. If the check box is selected, it should lock down another field called "Date Picked Up." The "Date Picked Up" field is for equipment repaired in house. Not only must the "Date Picked Up" field be locked down if the equipment is to be repaired off-site, I also need the "Date Picked Up" field's default value to be the "Date Finished" field. Hope that all makes sense. I have the check box working as far as locking it down if the ...

color chart
Hi all this is line chart with sale in money on vertical and quarters 01/08, 02/08,03/08,04/08 and 01/09.forecast,02/09.forecast on horizontal How can I color the chart wherre forecast is different that quarter in the past? Do I need macro or just using formula. Thanks in advance Daniel Ku So are you trying to compare forecast to forecast or forcast to actuals? Do you want to do it for all quarters, or just the current quarter? If you data is by month why are you comparing by quarter? If Jan 08 to Mar 08 were 1, 2, 1 and Apr 08 to Jun 08 was 2, 1, 1 would you consider this the sa...

rename invalid sheet name from access 97 report to Excel 2002
When I export a file from Access 97, my only choice is to export to Excel 97. I then open that file, with excel 2002, and I get an error message the the sheet had an invalid name and had to be renamed, but there is no apparent difference in the the name "Sheet1" in excel 97 or "sheet1" in Excel 2002. (I can find NO conversion sw, or find any way to "add to" my file types list. I am forced to use Access 97 with the office 2002 suite for now) Any Ideas on what is causing this? DLS 99 Can you open the file in xl97 to see what the sheet name is/was? Or do ...

System does not auto create Transfer In during Inter-store transfe
Hi, my customer running 2 stores and both store having the same interstore transfer option setting. my problem is 1 of the store does not auto generate Transfer In doc 1. store A - Transfer Out Doc store B - auto generate transfer In Doc store A uploaded 2 HQ message to HQ & store B received 2 HQ message. 2. store B - Transfer Out Doc store A - NOTHING GENERATED store B only uploaded 1 HQ message to HQ & store A received 1 HQ message. anyone got ideas how to solved this? Thanks Dennis Did you set the HQ Configuration options to auto gen...

Formatting Cells/Data
Hi, I hope you are doing well. I have a file I downloaded from our AS/400 (OS400 operating system). I open the file as a text file and go thru the wizard for converting the file to excel format. One of the columns/fields is numeric with some neg and pos numbers and some blank cell contents. During the wizard conversion the field is categorized as DOUBLE with no option to change the field. When I finish the wizard I save the file as an Excel workbook. Then I use the autosum option to calculate the total of the field/column; however, it only picks up on the positive #'s. Hence the t...

Bootstrapper has stopped working
Hello, I recently purchased a laptop with Windows Vista. I bought a licensed copy of MS Office 2007 Prof and am having a heck of time installing it- 10 days and still no installation! Last night I reset everything to Factory default, removed all trial versions, inserted my disk into the DVD RW and after a LONG wait and locking up the whole system received a message "Bootstrapper has stopped working". Found this possible fix on MS http://support.microsoft.com/kb/975735 , but got an "Error 40" when trying to install the fix! Any ideas? Have read elsewhere...

Condition Format syntax
I have pairs of cells to test. What I want to check is if the value of the 2nd cell is higher than the first, but less than 1.5 times as high. I've tried various syntax combinations using 'Formula is', but just can't seem to hit the right one (Excel 2003). Can anyone help? -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) Hi Paul, Try this one: =IF(B1>A1,IF(B1<A1*1.5,"Yes","No"),"No") Regards, Per "Paul Hyett" <vidcapper@invalid83261.co.uk> skrev i meddelelsen news:XeIMdj...

Boders Colors
How can I draw borders in other color, instead of black? Because I can only draw borders in black! Format > Cells > Border, you can select the colour from the Colo dropdown by clicking on the down arro -- Paul Sheppar ----------------------------------------------------------------------- Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2478 View this thread: http://www.excelforum.com/showthread.php?threadid=39362 ...

Transferring an order to an invoice...
We are transferring over to GP 10. I have a question about invoicing that I hope someone here can help me with. In GP 8, there was a transfer button on the Sales Order Transaction screen to transfer the order to an invoice. In GP 10, the transfer option is in a pull down menu. Can we make the transfer option a 3rd button on the screen? If someone could let me know, I would appreciate your help greatly! yes you should be able to do that if you wish using a button and a bit of vba code. patrick developer support -- This posting is provided "AS IS" with no warranties, a...

Time Formating
I need to know how to format a cell for time. I need to have the cell formatted so that when I type in the cell say 125211 or 12 52 11 it will automatically appear as 12:52:11. For a possible solution see http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards, Peo Sjoblom "Denise" <anonymous@discussions.microsoft.com> wrote in message news:4804E8F2-C978-48DC-B163-67029B218F7F@microsoft.com... > I need to know how to format a cell for time. I need to have the cell formatted so that when I type in the cell say 125211 or 12 52 11 it will automatically appear as ...

TRANSFER OUT TO TRANSFER OUT
I HAVE BEEN WORKING WITH RMS FOR OVER A YEAR AND HAVE NEVER SEEN THIS PROBLEM. STORE A MAKES A TRANSFER OUT AND STORE B RECEIVES A COMPUTER TRANSFER IN THE TRANSFER OUT WINDOW INSTEAD OF A TRANSFER IN. IN ADDITION TO THAT THE TRANSFER HAS BEEN CLOSED AT STORE B'S END AUTOMITICALLY AND IT IS EMPTY. AN KNOWLEDGE AS TO WHY THIS HAPPENS THANKS Check HQ Client for errors, or from HQ Manager, view the event log for that store. You'll probably find some errors at Store B on the 401 that created the transfer. I would guess that Store A used a transfer number with more than 14 c...

chart formating
My simple chart project has turned ugly. I have several charts linked from different files into my active spreadsheet. The x axis on all of these linked charts are date fields. However, on all of these linked charts a general number format is showing up not a date format. Is there anyway to have these numbers defaulted to a dated format? I've had problems with dates looking like numbers on axes in the past, and to get them to look like dates I had to go into the axis properties (right-click the axis or one of the numbers on the axis, then click on "format axis") an...

Fixed Asset transfer for a New Company
Is selling the Fixed Asset to the new company the best option to make a transfer to a new company? Can you give us a little more detail on what you are needing to accomplish? If two companies are different legal entities there are all kinds of other rules on 'transferring' assets. Is this just a new database or are you doing an intercompany transaction? We can help, we just need a little more explanation. "FA user" wrote: > Is selling the Fixed Asset to the new company the best option to make a > transfer to a new company? ...