+ Reply to Thread
Results 1 to 9 of 9

Macro to change cell background color depending on another cell's background color

  1. #1
    Registered User
    Join Date
    04-01-2016
    Location
    London
    MS-Off Ver
    Mac, 2016
    Posts
    4

    Cool Macro to change cell background color depending on another cell's background color

    Hi,

    I have an issue that I can't seem to find anyone else having. Usually, when you want a cell's background-color to change depending on another cell's background-color, you can write "If A1 has .ColorIndex = 5, change to .ColorIndex = 5" for all the relevant colors, and then that does the job.

    BUT, I need it to change to whichever color is the background-color of the cell, and not just some predetermined colors.

    I'm making an pattern that includes 4 colors, and the cells for each color in the pattern (that I guess I can distinguish by writing 1,2,3,4 in them) should change when the color is changed in a "panel" containing 4 colors.

    Just so you get the hang of it, I've made a smaller pattern and a panel on the left side, see the picture Screen Shot 2016-04-01 at 23.35.23.png
    Attached Images Attached Images
    Last edited by mari_m; 04-01-2016 at 06:35 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Macro to change cell background color depending on another cell's background color

    Hi,

    Why not just use Conditional Formatting and the 'Use a formula' option then
    e.g
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and set the background fill colour to yellow and apply the CF to the whole of your required cell range matrix.
    Create three other CFs for the other three colours.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    04-01-2016
    Location
    London
    MS-Off Ver
    Mac, 2016
    Posts
    4

    Re: Macro to change cell background color depending on another cell's background color

    That is a quick-fix, but not using the panel though, as I udnerstand it?

    I want it to automatically update when the colors of the panel is changed. By using CF like you say, I suppose I would have to enter the CF Panel each time to change the fill color

  4. #4
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    503

    Re: Macro to change cell background color depending on another cell's background color

    try this, someone with better knowledge could tidy the code up

    the core othe the code can be found here

    http://www.computing.net/answers/off...ell/14571.html

    Please Login or Register  to view this content.
    Last edited by Toonies; 04-01-2016 at 07:23 PM. Reason: original source entered

  5. #5
    Registered User
    Join Date
    04-01-2016
    Location
    London
    MS-Off Ver
    Mac, 2016
    Posts
    4

    Re: Macro to change cell background color depending on another cell's background color

    Thanks, Toonies! I've put the code into Excel now, but can't seem to get it working. Was there anything else I had to change other then myCellColor.Range?

    I now have this in Excel:
    Please Login or Register  to view this content.

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Macro to change cell background color depending on another cell's background color

    You may try something like this....

    The following code assumes that the color palette is defined in the Range("B2:B5") i.e. the color palette contains 4 colors in it. For visualization I have inserted the numbering in those color cells in the attached.

    So if you input a number from 1 to 4 in any cell on the sheet, the cell color will be changed to the defined color in the color palette. You may add more colors in the palette and change the range in Set paletteRng = Range("B2:B5") line of code.
    Please Login or Register  to view this content.
    For details, refer to the attached.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  7. #7
    Registered User
    Join Date
    04-01-2016
    Location
    London
    MS-Off Ver
    Mac, 2016
    Posts
    4

    Re: Macro to change cell background color depending on another cell's background color

    Yey, thanks, sktneer! This work.. almost! The only problem I'm experiencing is that once I change a color in the palette, it doesn't update in the aldready numbered cells (see picture). Is there a way to add something to the code that makes it check for updates or something?
    Screen Shot 2016-04-02 at 01.44.41.png

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Macro to change cell background color depending on another cell's background color

    Then try the following code.
    To make it work, after changing the color in the palette range, you need to re-input the no. again in the color palette cell i.e. if you change the color of the cell B2, after color change, reenter the number 1 in B2 and that will change the color of all the cells on the sheet which have 1 in them.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sktneer; 04-01-2016 at 09:08 PM.

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Macro to change cell background color depending on another cell's background color

    Are you saying that the matrix contains the numbers 1-4 which remain in place all the time and you just change the 4 colour definition cells and want the matrix to react accordingly?

+ 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. Automatic change background color depending on cell value
    By Surferdude949 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 05-22-2015, 08:02 PM
  2. 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
  3. Change cell background color based on another cells background color
    By Queo in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 06-10-2014, 05:28 AM
  4. [SOLVED] Macro to change all cells with a certain background color in another background color
    By kevinvzandvoort in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2012, 11:04 AM
  5. Macro to Change Cell Background Color From White to No Fill
    By emcdemc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-06-2009, 08:55 PM
  6. Change cell background color depending on cell value
    By fredrock80 in forum Excel General
    Replies: 1
    Last Post: 03-14-2005, 03:22 PM
  7. macro to change cell background color
    By rocket0612 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2005, 09:46 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