+ Reply to Thread
Results 1 to 12 of 12

Conditional formatting help needed

Hybrid View

  1. #1
    Registered User
    Join Date
    09-22-2019
    Location
    Portsmouth, UK
    MS-Off Ver
    Excel 2013
    Posts
    7

    Smile Conditional formatting help needed

    Hi, Im new to this forum and rusty with Excel, but hope you can help.

    I have a simple spreadsheet to record tasks in a log format. I have a date column A3 that shows the date a job is logged. I have a 'completed' column I3 that is blank until the completion date is entered. I want to be able to 'fill' the blank cells in the I3 column with colours representing the number of days after the date in column A3 as follows:

    The default fill for an empty cell I3 should be light green/black text
    If still blank 3 days after the date in A3, column I3 should change to orange.
    If still blank 5+ days after the date in A3, colum I3 should change to red.
    If a date is entered into I3 then the fill should return to the default (light green/black text).

    The above will give a visual representation of jobs slightly or seriously overdue.

    Hopefully the above makes sense however try as I have for two days I cannot get this to work.

    Thanks in anticipation.
    Last edited by AliGW; 09-22-2019 at 08:06 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,902

    Re: Conditional formatting help urgently needed

    Maybe
    Formula: copy to clipboard
    =AND(I3="", TODAY()-A3>=3)
    format orange
    And
    Formula: copy to clipboard
    =AND(I3="", TODAY()-A3>=5)
    format red
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-22-2019
    Location
    Portsmouth, UK
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Conditional formatting help urgently needed

    Very many thanks Trevor. Unfortunately your suggestion doesn't seem to work as I believe you can't have multiple conditional formats for a cell? Your suggestion seems to offer the change of colour once only - (I may well be wrong here or perhaps there's another way of achieveing the outcome). Just to be clear I'm looking for a progressive colour change for cells based on the time passed from the original A3 cell date. Thanks once again for your kind suggestion)

  4. #4
    Registered User
    Join Date
    09-22-2019
    Location
    Portsmouth, UK
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Conditional formatting help urgently needed

    As you were Trevor. It now works (operator error) - much appreciated for your help with this

  5. #5
    Registered User
    Join Date
    09-22-2019
    Location
    Portsmouth, UK
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Conditional formatting help urgently needed

    Apologies Trevor it seems to work except for the part where if a date entry is made to cell I3 - the colour doesn't change back to the default?

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,462

    Re: Conditional formatting help needed

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  7. #7
    Registered User
    Join Date
    09-22-2019
    Location
    Portsmouth, UK
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Conditional formatting help needed

    Many thanks Pepe - samlple attached. Two current issues:
    1. I cant work out how to make cell I3 go grey (default) when a date is entered.
    2. Ultimately I would prefer to highlight the complete row in the relevant colour but just the cell is workable.

    I hope that helps and have attached sample, thanks for any contribution.
    Mike
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,902

    Re: Conditional formatting help needed

    If the default, blank, is the same as when there is a date, you don't need CF ... just set the default colour for the range using cell format fill colour.

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,667

    Re: Conditional formatting help needed

    CF formulas
    For Red
    =AND($I3="",TODAY()>=$A3+7)

    For Orange
    =AND($I3="",TODAY()>=$A3+3,TODAY()<$A3+7)

    For Green
    =AND($I3="",TODAY()>=$A3,TODAY()<$A3+3)

    For Grey and text black
    =$I3<>""

    The formatting shown in file.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 09-23-2019 at 02:38 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  10. #10
    Registered User
    Join Date
    09-22-2019
    Location
    Portsmouth, UK
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Conditional formatting help needed

    Hi again Tim and thanks for your time.
    I have attached my SS file to show what I am trying to achive. Basically if the default cell colour is e.g. grey and as time passes the CF gives a colour e.g. + 5 days = red -
    when I type a date into the red coloured cell it reverts to orange (+ 3 days) when I want it to revert back to the default colour of grey on entry.
    I hope I've explained that well? I appreciate any further help you might provide and thanks once agin but this is way to complicated for novice like me to evaluate
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,667

    Re: Conditional formatting help needed

    Please see my previous post

  12. #12
    Registered User
    Join Date
    09-22-2019
    Location
    Portsmouth, UK
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Conditional formatting help needed

    Thanks so much to kvsrinivasamurthy - I wouldn't have considered the suggestion you made.
    Many 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. Chart Help Needed Urgently
    By mysticmoron109 in forum Excel General
    Replies: 1
    Last Post: 03-21-2018, 05:41 PM
  2. Help Needed Urgently with a formula
    By aaka in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-28-2015, 09:15 AM
  3. Simple help needed urgently!!
    By readingr in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-21-2010, 03:20 PM
  4. help needed urgently
    By richard354 in forum Excel General
    Replies: 3
    Last Post: 07-31-2006, 09:28 AM
  5. I'm a newbie, urgently help needed
    By eileen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2006, 01:23 PM
  6. Help Needed Urgently
    By John in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2005, 11:05 AM

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