1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Conditional Formatting question- simple

Discussion in 'Business Applications' started by singletrak, May 9, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. singletrak

    singletrak Thread Starter

    Joined:
    May 9, 2014
    Messages:
    17
    I have applied conditional formatting to a column of cells to highlight the cell red when the date contained in it is > 180 days. The formula applied to all of the cells is =TODAY()-180. Works perfect, however, there are cells with no values (dates) entered yet and they are also highlighted.

    Ideally I'd like the cells with no date in them to remain white until a date is entered and then apply the conditions after a date is entered.

    Thanks in advance for any help.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,965
    First Name:
    Wayne
    What cells are you using ?

    you would need to add the cell to your =TODAY()-180
    =AND( TODAY()-180, Cell<>"")
     
  3. singletrak

    singletrak Thread Starter

    Joined:
    May 9, 2014
    Messages:
    17
    The cells I'm using are I46-I57. I46 is the only cell with a date now but eventually the rest will be populated and I'd like the formula to apply to them once the date is entered. So, can you provide and example of the formula?

    I have several other columns that I'll be able to apply this to. Thank you!
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,965
    First Name:
    Wayne
    as you are going back 180 days with

    I46<Today()-180

    =AND( I46<TODAY()-180, I46l<>"")

    can you expand on this - do you want to fill other columns based on I46 - OR - will they have a date and be independent

    for 2007 or 2010 excel version
    Conditional Formatting

    Highlight applicable range >>
    I46:I57

    Home Tab >> Styles >> Conditional Formatting
    New Rule >> Use a formula to determine which cells to format
    Edit the Rule Description: Format values where this formula is true:

    =AND( I46<TODAY()-180, I46<>"")

    Format&#8230; [Number, Font, Border, Fill]
    choose the format you would like to apply when the condition is true
    OK >> OK
     
  5. singletrak

    singletrak Thread Starter

    Joined:
    May 9, 2014
    Messages:
    17
    The other columns will be totally separate. To be specific, I work in a hospital and each column represents a different lab which needs to be done every 6 months (Ex: "I" column is for liver function tests, "J" column will be for blood counts etc.). I'd like to enter the date the last lab was drawn and have it highlight when it becomes 180 days or longer since it was last done.

    The blank cells represent a lab that wasn't drawn yet or just has not been filled in yet. I have to look them up indivudually and fill in as I go. I just don't want the blank cells to be highlighted. Thank you.
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,965
    First Name:
    Wayne
    in which case the formula I provided should work ok for you

    if not perhaps a sample excel sheet would help us
     
  7. singletrak

    singletrak Thread Starter

    Joined:
    May 9, 2014
    Messages:
    17
    Sorry - Excel 2007 and they cells are independent of I46.
     
  8. singletrak

    singletrak Thread Starter

    Joined:
    May 9, 2014
    Messages:
    17
    Works Like a charm!
    Thanks!
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,965
    First Name:
    Wayne
    your welcome
     
  10. singletrak

    singletrak Thread Starter

    Joined:
    May 9, 2014
    Messages:
    17
    The solution has been working great all day.

    A new issue came up. For the lab values I need to highlight values when they are outside of a range (Ex: any value not between 30-80 need to be highlighted). I tried =ABS(C2)>D2 where C2=first value and D2=second value but that obviously incorrect.
     
  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,965
    First Name:
    Wayne
    when you say first and 2nd value - can you explain a little more

    to get a value between 30 and 80
    assume the value is in A1

    then

    =AND( A1>=30, A1<=80)
     
  12. singletrak

    singletrak Thread Starter

    Joined:
    May 9, 2014
    Messages:
    17
    First value = low end of range (30) Second value = high end of range (80). I'd like any cell containing a number below 30 or above 80 to be highlighted. Also cells with no value should remain white.

    Thank you so much for your time btw. You've made my work today more productive.
     
  13. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,965
    First Name:
    Wayne
    =AND( A1>=$C$2, A1<=$D$2)

    The $ make sure that when checking other cells , the C2 and D2 do not change - called absolute reference rather than relative reference

    should not change if cell is blank as that would be seen as 0 and not in the range
    but just incase

    =AND( A1>=$C$2, A1<=$D$2, A1<>"")
     
  14. singletrak

    singletrak Thread Starter

    Joined:
    May 9, 2014
    Messages:
    17
    =AND( A1>=$C$2, A1<=$D$2, A1<>"")[/QUOTE]

    Sorry just got back to this project. I keep getting an error message with this formula.

    My range is 30-80 ( numbers outside this range should be highlighted empty cells remain white) and the first cell with data is D46

    So I tried =AND(D46>=$3$0,D46<=$8$0,D46<>"") Should AND be "ABS" or do I have this entirely wrong?

    Thank you.
     
  15. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,965
    First Name:
    Wayne
    where C2=first value and D2=second value
    so in this case
    C2 =30 and D2 =80
    correct ?
    =AND( D46>=$C$2, D46<=$D$2, D46<>"")
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1125690

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice