+ Reply to Thread
Results 1 to 3 of 3

Stopping Conditional Formatting until subject cells have been populated

  1. #1
    Registered User
    Join Date
    08-12-2010
    Location
    York, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Stopping Conditional Formatting until subject cells have been populated

    Hi
    I am setting up a sheet that will change the colour of a particular row when a task is completed within a certain time frame which is also sourced from the same row but another cell. Basically I have a start time, end time and acceptable timeframe. The row will change green if the difference between the start time and the end time is within the acceptable timeframe and red if it is not.

    My problem is that as soon as I input the start time the row turns green. I want it to be that the row remains 'no colour' until the three cells that populate the conditions are filled.

    Current conditions are
    condition 1 formula is=$A3>($C3-$B3) then fill colour green
    condition 2 formula is=$A3<($C3-$B3) then fill colour red
    condition 3 formula is=ISBLANK then no fill colour

    where A3 is acceptable timeframe, C3 is endtime, B3 is startime

    As soon as I enter a time into B3 then the row turns green when I would like no change in colour until all cells have information in them.

    Any help would be appreciated.

    Thanks.
    Last edited by claretblue; 08-12-2010 at 06:25 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Stopping Conditional Formatting until subject cells have been populated

    count the cells that have been filled in:

    =counta($A3:$C3)=3

    condition 1 formula is=and(counta($A3:$C3)=3,$A3>($C3-$B3)) then fill colour green
    etc

  3. #3
    Registered User
    Join Date
    08-12-2010
    Location
    York, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Stopping Conditional Formatting until subject cells have been populated

    Excellent!!

    Thank you very much for your very fast response and great help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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