Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have several worksheets in microsoft excel of the same format.

In every worksheet I want:

  • The first row one light green
  • The next four sheets in light yellow
  • The next three in light blue
  • etc.

At the moment, I am doing this manually for every worksheet and I thinking there has to be better way.

Any tips?

share|improve this question
2  
methinks you've mistyped something... sheets? within a worksheet? Rows? Do the first sheet, then highlight a single representative column containing all the color changes you've made... then click on the paintbrush looking icon (format painter), then click on the destination worksheet:column/cells. It should copy the colors over. BE CAREFUL, this can ALSO copy number formats, date formats and such. IIRC, there's a selector to allow you to choose what to copy. Play with it. Saves loads of work. –  lornix Jun 21 '12 at 9:04
    
Thanks. This only can be used once. I need to "paste" the colour format for 4 worksheets. So I have to go back and select again unless you have another smart tip. –  dublintech Jun 21 '12 at 9:13
    
Well, you only have to select one column (if you're coloring the rows differently, I would have though the reverse, selecting rows... your data) So click on "G", paintbrush, change sheets, click on "G1" ... seems a far cry with less potential error than by hand. No worries. Glad to help. –  lornix Jun 21 '12 at 9:32
    
I will accept your suggestion as an answer lornix –  dublintech Jun 21 '12 at 9:51
    
In the 2nd line, is that "next four SHEETS" or did you mean ROWS? –  Ellesa Jun 21 '12 at 9:57

4 Answers 4

Write a VBA macro like this:

Sub temp()
    Range("A1:G11").Select
    Selection.Copy
    'Modify this part to loop over contents of your choice
    Range("J17:S35").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    'Modify upto this
End Sub

Now, modify the paste component of this macro to loop over the contents of your choice.

share|improve this answer
    
Thanks but lornix has given me what I need. –  dublintech Jun 21 '12 at 9:51

There's an icon (somewhere) on the Excel toolbar, looks like a paintbrush, used to copy formatting (colors, dates, numbers, etc) from one place to another.

  • Color the first sheet as you desire
  • highlight a single representative column containing all the color changes you've made
  • click on the paintbrush looking icon (format painter)
  • click on the destination worksheet:column/cells.

It should copy the colors over.

BE CAREFUL as this can ALSO copy number formats, date formats and such. IIRC, there's a selector to allow you to choose what to copy. Play with it. Saves loads of work.

share|improve this answer
    
that icon is called "Format Painter" and it is on Home tab in Clipboard section. @dublintech: this will be useful only if number of sheets is small.. if you need to do this same sheets and same cells, its better to write macro....it will do your job with a single click... –  tumchaaditya Jun 21 '12 at 12:27
    
That's the name! Thank you. –  lornix Jun 21 '12 at 22:38

Excel lets you format multiple sheets at the same time.

  1. Select all four sheets. You may do this by clicking each sheet you wish to format while holding Ctrl
  2. Color the cells as you wish.

Click one of the sheet tabs to "deselect." All four sheets should have the same color formatting when you're done.

Example:

enter image description here

share|improve this answer

Similar to Format Painter method suggested by lornix, here is another method that may save you some time.

  1. Format Sheet1 as desired
  2. Ctrl-A to select all or select desire row
  3. Ctrl-C to copy
  4. Ctrl-PgDn to go to next sheet
  5. Ctrl-Home to go to cell A1
  6. Alt-E > S > T > Enter to paste format only
  7. Repeat step 4-6 until completed

Step 6

It means Edit (Alt-E) > Paste Special S > Format T > OK Enter.
It is tested to work in Excel 2003, and should work also in later versions (not tested).

In case it doesn't work in your version, you can also try Menu > S > T > Enter.
Menu key is usually next to Win key on keyboard which will bring up the right click menu in Windows.

Note

This method not only copies the colour format, but all formats (including number format, etc).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.