+ Reply to Thread
Results 1 to 10 of 10

[SOLVED] Pasting Only Background Colors Across Rows

  1. #1
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    [SOLVED] Pasting Only Background Colors Across Rows

    So, I've been trying to figure this one out for a while...

    I have a column of data that has several different background colors assigned to each cell. For example, A2 is blue, A3 is orange, A4 through A10 is yellow, and then A11 is blue again.

    I would like to copy and paste these background colors across all the rows in the spreadsheet; however, if I simply copy and paste special > formats, it also pastes the column width and other formatting options from column A that I do not wish to have changed on my spreadsheet.

    I have done some research on this and I now know that each color is assigned a number and I can have the number returned using a function or a macro (I'm not really certain of the difference or if there even is one) and then use that value to then set the background color of each row accordingly.

    The problem is that even after reading articles like this and this, I still can't seem to figure out how to put the information together to do what I want to do.

    Can anyone interpret this for me and possibly produce a code that does what I am needing?

    Thanks in advance!

    -Amy Kate
    Last edited by NBVC; 02-22-2010 at 12:51 PM. Reason: Mark Solved per OP request

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,452

    Re: Pasting Only Background Colors Across Rows

    This code will transfer the colour of the selected cells to the whole row.

    Please Login or Register  to view this content.
    To input the code, use ALT & F11 to start the VBA editor

    Go to Insert, Module

    Paste the code

    Close the VB editor

    In excel, select the cells from which you want to transfer the colour.

    Go to Macros, run macro, and select copy_colours_across_rows
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Pasting Only Background Colors Across Rows

    Wow, that was way more simple than what I was trying to do! So what word(s) do I need to change if I want to do the same for the font color instead?

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,452

    Re: Pasting Only Background Colors Across Rows

    Change this:

    Please Login or Register  to view this content.
    For this:

    Please Login or Register  to view this content.
    If you want to find out how to adjust formatting with VBA, record a macro in which you alter the formatting, then take a look at it in the VBA editor.

  5. #5
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Pasting Only Background Colors Across Rows

    So do you know how I could tell it to only do that down as many rows as there is data? Right now, I have it set to go down 2000 rows just so nothing is left off, but it takes forever and most of the time there is only around 100 rows.

  6. #6
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,452

    Re: Pasting Only Background Colors Across Rows

    Presuming your data starts at A1, and has no gaps



    Please Login or Register  to view this content.

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Pasting Only Background Colors Across Rows

    Presuming your data starts at A1, and has no gaps
    Slight modification to Sweep's code. If you ever have blank cells between the first and last row, selection of the range will omit rows below the empty cell and thus cause some rows to not get the color change.

    This code mod will select the range to the last used row in column-A and will not be affected by blank cells in between.
    Please Login or Register  to view this content.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  8. #8
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Pasting Only Background Colors Across Rows

    Thanks sweep, that worked great, and thanks for the modification Palmetto.

    Can someone mark this solved for me, since it is an old post?

  9. #9
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Pasting Only Background Colors Across Rows

    Can someone mark this solved for me, since it is an old post?
    Just go to the top of YOUR first post, select Thread Tools, Go Advanced, then choose the [SOLVED] prefix.

  10. #10
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Pasting Only Background Colors Across Rows

    Quote Originally Posted by Palmetto View Post
    Just go to the top of YOUR first post, select Thread Tools, Go Advanced, then choose the [SOLVED] prefix.
    Go Advanced is not an option under "Thread Tools."

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1