+ Reply to Thread
Results 1 to 13 of 13

Conditional formatting to make a cell green if a date appears.

  1. #1
    Registered User
    Join Date
    10-06-2017
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2016
    Posts
    13

    Conditional formatting to make a cell green if a date appears.

    Hello,

    I am creating a training database with an overview sheet. Sheet 1 is the overview and the sheet 2 and on are the training database for various types of training.
    I have a formula that changes a cell on Sheet 1 orange when the expiration date in Sheet 2 D5 is 45 days from today and red when the expiration date has passed. I can't figure out the formula however to make the overview sheet cell green when a date of training has been entered in Sheet 2 B5.

    Also am I correct that if I order the rules in conditional formatting, I can have the last rule over right the ones before it?

    Any help is greatly appreciated. Thank you

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,829

    Re: Conditional formatting to make a cell green if a date appears.

    To have a cell turn green if a date exists in another cell, use this CF formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where A1 is the cell which might or might not contain a date.
    Note that this will turn the CF'd cell green if any number is in A1, so you may wish to consider Data Validation to allow only dates to be entered.


    In answer to your other question, yes, if you re-order the rules they'll overwrite each other. Go to Conditional FormattingManage Rules. The rules will be applied from bottom to top.

    Hope that helps.
    Regards,
    Aardigspook

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    10-06-2017
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Conditional formatting to make a cell green if a date appears.

    Thank you so much for your help, very much appreciated. Worked perfectly.

  4. #4
    Registered User
    Join Date
    10-06-2017
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Conditional formatting to make a cell green if a date appears.

    The formulas all work just as they are supposed to. However, I'm wondering if there is a way to put the CF in all the cells of a column instead of adding three rules to each cell as I have a lot of cells and this could take hours. I tried setting up 3 cells and then dragging over the rest of the column, I also tried the Format Painter, both didn't work however as cells got linked to the copied cells. For example I set up Cell B15,B16 & B17, B12 got linked to B15 - B13 to B16 & B4 to B17. THis meant that when Sheet 2 B14 was changed it effected Sheet 1 B15 and B13.

    Sheet 2.PNG
    Sheet 1.PNG
    CF.PNG

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,829

    Re: Conditional formatting to make a cell green if a date appears.

    Yes - use the Applies to box in the Manage Rules dialogue box (which you can see in your screenshot).

    First, set up the rules for the top-left of the range you want to apply the CF to - for example, if your range is B2:H50, then do the rules for cell B2.
    Then, in the Applies to box, enter the whole range - for example, $B$2:$H$50.

    That will apply the same rule(s) to the whole range. Don't worry about changing the CF formula - Excel will automatically apply the rule to the appropriate cell (that is, if your formula is =B2="a" then Excel will read this as =C2="a" for C2, etc.

    Hope that makes sense.


    p.s. Thanks for the rep.

  6. #6
    Registered User
    Join Date
    10-06-2017
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Conditional formatting to make a cell green if a date appears.

    That worked and saved so much time, thank you. The only problem I'm having now though is that if the cell is blank on Sheet 2, instead of being blank on Sheet 1, like it is when I manually enter the CF, it is now green. Is there a way of getting the formula you originally gave me to stop working if the date is removed? or do you have any other trick that would work?

    Thanks again,

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,829

    Re: Conditional formatting to make a cell green if a date appears.

    If the date is deleted, the green should disappear. The two reasons I can think of why it wouldn't are:
    (1) the cell is shaded manually
    (2) there is another CF rule being applied

    (1) is easy to check/fix - just look to see if the cell is shaded and if it is, change it to 'No Fill' (Home tab, Font section, Fill color button).
    (2) may be a legacy of old CF rules, as you said you had some problems with cells referring to the wrong cells. Use the Manage Rules dialogue box to remove any old/incorrect rules. If you only have a few rules which you want to have applied, it may be easiest to wipe all the old CF rules and start again. You can do this using Conditional FormattingClear RulesClear rules from entire sheet. Then re-input the CF rules you want.

    If neither of those help, then please attach a sample workbook, with just enough data in it to show what's happening. Make sure your desired results are shown: either mock them up manually or make some notes on the file to show what you want where. Remember to remove any confidential data or randomise it - for example if names are specifically needed to illustrate a problem, then change them to random names, etc.
    Just in case, make a back-up copy of your actual file to create the sample file from.
    To upload a file, click on Go Advanced under the reply box, scroll down and click Manage Attachments, use the Browse button to locate your sample file and upload it.

  8. #8
    Registered User
    Join Date
    10-06-2017
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Conditional formatting to make a cell green if a date appears.

    I cleared all formulas and then reapplied the ones I want. I also cleared all previous shading. This didn't work. I did notice that if I start the formula with Sheet 1 B5 everything works as I want because Sheet 2 B6 is telling it to stay blank.
    The problem with that though is that once Sheet 2 B6 has a date I will be in the same predicament. I have attached a small version of the spreadsheet so you can see what I have done and probably see right away what is wrong.

    Thank you so much for all of the time you are spending helping me with this, I really appreciate it.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,829

    Re: Conditional formatting to make a cell green if a date appears.

    Sorry, been visiting my father-in-law in hospital, so haven't had much time. I'll try to look at this in the morning. It's probably just a cell-referencing issue. Sometimes they're a bit tricky to work out if you haven't used CF much.

  10. #10
    Registered User
    Join Date
    10-06-2017
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Conditional formatting to make a cell green if a date appears.

    Take all the time you need. I just really appreciate your help. Your father-in-law is much more important than this spreadsheet.

  11. #11
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,829

    Re: Conditional formatting to make a cell green if a date appears.

    Okay, it was a combination of background fill and an absolute cell reference.

    1. Select B4:B28 (on the 'Overview' sheet);
    2. On the Home tab, Font section (second from the left), click the Fill color button and select No Fill;
    (you won't see any difference yet);
    3. Click Conditional FormattingManage Rules, select the third rule (the green one) and click Edit Rule;
    4. Remove the second $ sign in the cell reference; i.e. the formula should be =ISNUMBER(Forklift!$B5) instead of =ISNUMBER(Forklift!$B$5).

    The cells without dates on the Forklift sheet should now be blank.

    The problem was that the green CF rule was looking at cell D5 only. The $ sign, as I'm sure you know, makes the reference absolute, so removing it makes the CF rule look at each row (5,6,7,...) instead of being 'locked' to row 5.

    Hope that makes sense!

  12. #12
    Registered User
    Join Date
    10-06-2017
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2016
    Posts
    13

    Re: Conditional formatting to make a cell green if a date appears.

    I can't say thank you enough for all of your help. I'm not permitted to give you any more Rep just yet, or I would. I didn't know the rule for the $. I'm learning as I go, thanks for the lesson.

  13. #13
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,829

    Re: Conditional formatting to make a cell green if a date appears.

    You're welcome. Glad I could help.
    (And don't worry about the rep - a thank you is enough!)

+ 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