+ Reply to Thread
Results 1 to 8 of 8

copy cell background color in range in one sheet to the same range in another sheet

  1. #1
    Registered User
    Join Date
    09-17-2024
    Location
    Prince Edward Island, Canada
    MS-Off Ver
    365
    Posts
    4

    copy cell background color in range in one sheet to the same range in another sheet

    Hello,
    I have next to no experience with vba. I had found some code years ago that did what I am looking for, but have since lost it.

    I want to be able to manually change the background color of cells or a whole row in my 'Main' sheet, and have the same color show up in the same cells / row in a second and third sheet. Not conditional formatting, I do the color change directly in the 'Main' sheet.

    I use the second, third, etc. sheets as copies of the 'Main' sheet, and have a filter on a column so that it shows only the information for a particular salesperson. One salesperson per sheet (besides 'Main'). I do not enter data directly into these additional sheets, direct data entry is done in 'Main'.

    I have the following code, but it's not working right:
    Please Login or Register  to view this content.
    Administrator's note: Please take the time to review our rules. There aren't many, and they are all important. Our guidelines recommend code tags. I have added them for you this time because you are a new member. --6StringJazzer

    With this, the cell color match works fine in the 'John Details' and 'Neal Details' sheets, but does not work at all in the 'Hans Details" sheet - all cells in the 'Hans Details' sheet still have no background color.

    Any ideas?

    Thank you!
    Last edited by 6StringJazzer; 09-17-2024 at 10:46 AM. Reason: please use code tags

  2. #2
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    2,617

    Re: copy cell background color in range in one sheet to the same range in another sheet

    Does this work for you?


    When you activate a sheet, if it's not Main it'll copy the formats from Main, unless you have copied something.
    Changing a colour doesn't trigger the Change Event so the colourisation will only occur when the sheet is activated.

    In the code for ThisWorkbook.
    Please Login or Register  to view this content.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    26,143

    Re: copy cell background color in range in one sheet to the same range in another sheet

    In this code, actually, Hans is the only sheet that should work correctly.

    Please Login or Register  to view this content.
    Never do this if you don't know exactly what error you are expecting. If an error occurs you will never know about it. For example, if the sheet is Jan Details but you misspelled it in your code as Hans Details, you will never know.

    The variable names are very cryptic and so make the code very confusing to read. It is necessary to keep checking back and forth. This has caused you to misspell variable names
    Please Login or Register  to view this content.
    The second line is causing an error because xI3DRg is not defined, and you are suppressing the error. If you used Option Explicit you would have found this error. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

    ALSO

    This line of code is not causing your problem but it doesn't do what you think it does:
    Please Login or Register  to view this content.
    In a Dim statement, each variable must get its own data type. Onl xID3Rg is a Range variable. All the other ones will default to Variant. Do this:
    Please Login or Register  to view this content.
    Indentation and spacing in the code make it much easier to read.

    I have provided a more readable, correct, and tested version of your code.

    Please Login or Register  to view this content.
    What other formatting is there? Is there formatting in Main that you do not want to copy? If not, then this is much simpler and much faster than your approach.
    Please Login or Register  to view this content.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    09-17-2024
    Location
    Prince Edward Island, Canada
    MS-Off Ver
    365
    Posts
    4

    Re: copy cell background color in range in one sheet to the same range in another sheet

    ByteMarks, sorry, that did not seem to work. After putting in that code, when I changed cell background colors in the 'Main' sheet, nothing changed in the other sheets.
    Last edited by sbweaver; 09-17-2024 at 11:25 AM.

  5. #5
    Registered User
    Join Date
    09-17-2024
    Location
    Prince Edward Island, Canada
    MS-Off Ver
    365
    Posts
    4

    Re: copy cell background color in range in one sheet to the same range in another sheet

    6StringJazzer, thank you! Both of those solutions work, sort of.

    The first solution slows down Excel's response to anything I do (have to wait 5 seconds to tab to the next cell, or click anywhere else).

    The second solution doesn't cause that slow-down. But in the Main sheet, the page kind of flashes or refreshes 3 or 4 times quickly every time after I enter something in a cell then move to the next cell. As well, when I go to other sheets, the whole range shows up as selected, instead of one single cell being the 'target' or whatever you would call that when you first land there.

    Thanks!

  6. #6
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    2,617

    Re: copy cell background color in range in one sheet to the same range in another sheet

    See attached.

    You could also do this manually: Starting on Main, hold shift and select the other sheets. Any changes you make will be done to all of them.
    Attached Files Attached Files

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    26,143

    Re: copy cell background color in range in one sheet to the same range in another sheet

    Think about providing your file for testing.

    It looks like you started out by using
    Please Login or Register  to view this content.
    in sheet Main, which will copy the formatting every time you click the mouse anywhere in sheet Main. Yes, that will slow you down.

    The other option is to change the code to be triggered by a button click.

  8. #8
    Registered User
    Join Date
    09-17-2024
    Location
    Prince Edward Island, Canada
    MS-Off Ver
    365
    Posts
    4

    Re: copy cell background color in range in one sheet to the same range in another sheet

    ByteMarks - Ok, I see what you mean now. I wasn't familiar with activating sheets beforehand. That seems to work, if I remember to activate/select all the other sheets before changing cell colors in the Main sheet. Thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 03-22-2023, 07:12 PM
  2. [SOLVED] copy background color from one sheet to another if text match
    By martin_ in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 09-30-2017, 04:28 AM
  3. Replies: 0
    Last Post: 06-24-2015, 06:26 AM
  4. Change a Range's cell background color to that of another cell's cond. format color
    By kamelkid2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-31-2014, 12:57 PM
  5. Replies: 4
    Last Post: 09-24-2013, 08:57 AM
  6. Copy Cell Range From Previous Sheet and Paste to Active Sheet
    By jtal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-01-2013, 03:26 PM
  7. [SOLVED] Macro: Dynamic creation of sheet based on cell value then copy range to the new sheet
    By BeachRock in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-15-2012, 11:31 AM

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