+ Reply to Thread
Results 1 to 10 of 10

Color a cell in a table depending on the date on a separate column

  1. #1
    Registered User
    Join Date
    06-14-2012
    Location
    Italy
    MS-Off Ver
    Excel 16.0
    Posts
    42

    Question Color a cell in a table depending on the date on a separate column

    Hi, I don't know whether this can be done with a simple conditional formatting, let's ask the experts

    I have this table with dates on 1st row and column, and I'd like the table cells to be color-filled if a date on row 1 matches one I manually insert on column A.

    To make it more difficult (but this is a 2nd step), it should be orange-filled if the date on column A is "Temp", green-filled if "Final". Temp and Final info are on column B.

    I'm attaching the spreadsheet I'm working on. I've manually added the orange / green colouring where it should go.
    Also is there a way for manual formatting to prevail on the conditional one? I.e. greening cell BI6 manually while there's a rule greying all weekend cells?

    Thanks for your help!
    Attached Files Attached Files
    Last edited by xlepws; 05-26-2020 at 01:41 PM. Reason: solved!

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: color a cell in a table depending on the date on a separate column

    Select C2:BK10 & use this for orange
    =AND(B2="Temp",$A2=C$1)

    Similar for green just change it to Final

  3. #3
    Registered User
    Join Date
    06-14-2012
    Location
    Italy
    MS-Off Ver
    Excel 16.0
    Posts
    42

    Re: color a cell in a table depending on the date on a separate column

    Thank you, it works!
    Last edited by xlepws; 05-26-2020 at 05:52 AM.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: color a cell in a table depending on the date on a separate column

    You're welcome & thanks for the feedback.

  5. #5
    Registered User
    Join Date
    06-14-2012
    Location
    Italy
    MS-Off Ver
    Excel 16.0
    Posts
    42

    Re: color a cell in a table depending on the date on a separate column

    Me again..I've tried to colour a "range" between two dates, but it doesn't seem to work.
    I used this formula: =AND($D1>=$A2;$D1<=$B2), but the result is
    cond_format_error_coloring_range.png
    ..while I just wanted cells from Q2 to AR2 to highlight in blue..

    What am I doing wrong? Thanks for your help!
    Attached Images Attached Images

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: color a cell in a table depending on the date on a separate column

    How about
    =AND(D$1>=$A2,D$1<=$B2)

  7. #7
    Registered User
    Join Date
    06-14-2012
    Location
    Italy
    MS-Off Ver
    Excel 16.0
    Posts
    42

    Re: color a cell in a table depending on the date on a separate column

    You are right, of course, I should have locked row 1 Thanks again!

    Now, last question, which I posed in 1st post:
    is there a setting for manual formatting to prevail on the conditional one? I.e. greening cell BI6 manually while there's a rule greying all weekend cells?

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: color a cell in a table depending on the date on a separate column

    You can create a name in name manager call it IsColoured & in the refers to box put =GET.CELL(63,INDIRECT("rc",FALSE))

    Save is as a macro enabled workbook & change the weekend formula to
    =AND(IsColoured=0,WEEKDAY(D$1,2)>5)

    Macros will need to be enabled for this to work.

  9. #9
    Registered User
    Join Date
    06-14-2012
    Location
    Italy
    MS-Off Ver
    Excel 16.0
    Posts
    42

    Re: color a cell in a table depending on the date on a separate column

    Thank you!

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Color a cell in a table depending on the date on a separate column

    You're welcome.

+ 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: 1
    Last Post: 09-18-2018, 04:57 PM
  2. Replies: 4
    Last Post: 03-11-2015, 08:46 AM
  3. [SOLVED] Change cell color depending on cell color, error in code
    By mushedpotatos in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2014, 04:20 AM
  4. [SOLVED] Macro to update the date column for certain rows in a table depending on a column value
    By ajolin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-18-2012, 12:21 AM
  5. Macro that is column specific & changes cell color depending on cell contents
    By zurispowell in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2012, 05:03 PM
  6. Replies: 1
    Last Post: 04-18-2011, 06:38 PM
  7. Replies: 3
    Last Post: 07-08-2010, 06:48 PM

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