+ Reply to Thread
Results 1 to 13 of 13

Conditional formatting based on colour of cells

  1. #1
    Registered User
    Join Date
    01-21-2021
    Location
    Leeds
    MS-Off Ver
    Office 2016
    Posts
    7

    Conditional formatting based on colour of cells

    Hi, I'm trying to set up some conditional formatting based on what the colour of 4 other cells are in the same row, I'm trying to get it so that if any of them are a specific colour then it will change the colour of the cell I'm doing the formatting on. I'm using Office 2016.

    For example, I want A2 to turn red if B2, C2, D2 or E2 are red, if they are all green I want A2 to stay the same colour, if only one of them is red I want it to turn red, I hope that makes sense. I've got conditional formatting on the B:E columns that turn them green if the dates are within this month or last month, if they aren't then they don't change colour. But the cells themselves are coloured red by default.

    I've tried one thing I found online but it hasn't worked. I tried to post a link to the method but it won't allow me as this is my first post.

    Thanks in advance for any help provided

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,324

    Re: Conditional formatting based on colour of cells

    Welcome to the forum.

    What turns the feeder cells red? If it's manually applied formatting, then it cannot be used in a CF rule.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    We don't need links to things you've looked at online - explain here what you have tried that did not work. Nobody should have to follow links in order to be able to help you!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Conditional formatting based on colour of cells

    As Ali has said, it would help if you attached a sample Excel workbook. If you have CF on cells in columns B to E to turn them green, then perhaps you can use those conditions (or the inverse of them) to change the colour in column A, but we will need to see your workbook to determine how to do this.

    Pete

  4. #4
    Registered User
    Join Date
    01-21-2021
    Location
    Leeds
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Conditional formatting based on colour of cells

    Hi, I've added an example workbook. The red colour is manually applied because I couldn't get it to turn red based on conditional formatting because the formatting options were limited. The turning green is triggered by any dates that are not in the last month or the current month.

    The method that I was trying to follow was to create a function to identify the colour in the cells. When I tried to do that though I was either not doing it right or it just wasn't working. The attempted function will be accessible when hitting Alt F11, if it doesn't come up automatically on the example it is Module1 under modules.

    Thanks again for the help here, I really appreciate it
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,324

    Re: Conditional formatting based on colour of cells

    What are the rules for a cell to turn red, please? Tell us in words what the logic is.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Conditional formatting based on colour of cells

    Ali, the cells are coloured red manually, so there are no rules for that, and then the CF rules change these to green if the dates are within last month to this month.

    @Matt,

    You have different rules applied to the different columns. In particular, some NULLs are shown green and some as red. Do you want all NULLs to be treated as Green?

    Also, are you only concerned with the month, or do you want to take into account the year as well?

    Pete

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Conditional formatting based on colour of cells

    @Matt,

    you will need to set up conditional formatting on the cells from A2 down, using this formula:

    =SUMPRODUCT((TEXT($B2:$E2,"mmyy")=TEXT(TODAY(),"mmyy"))+(TEXT($B2:$E2,"mmyy")=TEXT(EOMONTH(TODAY(),-1),"mmyy"))+($B2:$E2="NULL"))=4

    to turn the cells red. This takes into account the year, and also treats NULLs as if they are coloured green, but you will need to amend your CF rules in columns B to E to be more consistent.

    Hope this helps.

    Pete

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,324

    Re: Conditional formatting based on colour of cells

    Ali, the cells are coloured red manually, so there are no rules for that,
    Yep, I know that - I wanted to know what the circumstances for red would be, but I now realise that was mentioned in the opening post.

    I have to say, the workbook nearly gave me a migraine when I opened it. If all of that shading is REALLY necessary (I doubt it is), then the OP would be well advised to go for less aggressive tomes. The text is hard to read, too.

    I'll leave it to you, given that I don't want to bring on a headache.

  9. #9
    Registered User
    Join Date
    01-21-2021
    Location
    Leeds
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Conditional formatting based on colour of cells

    Hi Pete,

    The differing rules on the NULL are just to do with the section that the last column covers, if they have never used it before we aren't bothered about seeing that, but if they haven't used the other sections before we do want that flagging up. It's a last usage report so the way I've got it is that it puts any dates in the last month and the current month as green because we want to flag up anyone who hasn't used sections in over a month ago.

    Thank you for that formula, I'll try that and let you know Can I change that formula to ignore the NULLs in column E? Or do they have to be the same range on each?

    EDIT: I tried to quote your posts but the forum wouldn't allow me to post the reply with the quotes saying I can't post links again, there weren't any links though which was odd lol.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Conditional formatting based on colour of cells

    The arrays have to be the same size (i.e. 4 columns wide), but you can change the range in the third term to start at column A if it doesn't matter about NULLs in column E, i.e. the third term would become:

    ... +($A2:$D2="NULL") ...

    Hope this helps.

    Pete

  11. #11
    Registered User
    Join Date
    01-21-2021
    Location
    Leeds
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Conditional formatting based on colour of cells

    Hi Pete, thank you so much! That worked a treat! I really appreciate your help on this
    Last edited by AliGW; 01-22-2021 at 08:59 AM. Reason: PLEASE don't quote unnecessarily!

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Conditional formatting based on colour of cells

    Glad to help, Matt.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  13. #13
    Registered User
    Join Date
    01-21-2021
    Location
    Leeds
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Conditional formatting based on colour of cells

    Marked it as solved now, thanks I'd already added to your reputation for the help

+ 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: 3
    Last Post: 07-18-2019, 11:16 AM
  2. [SOLVED] Sum if cells match a colour based on conditional formatting
    By misterbaker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2018, 04:21 AM
  3. conditional formatting fill cells based on text, how to find sum based on CF colour
    By tubbybear in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-27-2016, 12:04 PM
  4. Help doing conditional formatting based on size cell to colour other cells
    By Craigsim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2015, 06:09 AM
  5. Count Cells Based on Colour Determined by Conditional Formatting
    By RanCanMan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2015, 09:45 AM
  6. Count blank cells by colour based on conditional formatting
    By mb0202 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2013, 07:37 AM
  7. [SOLVED] Conditional Formatting (?) based upon another cells colour
    By bellevue in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-09-2012, 04:27 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