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 cell color change using an IF statement

Discussion in 'Business Applications' started by Niessen, Feb 27, 2009.

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

    Niessen Thread Starter

    Joined:
    Jul 31, 2005
    Messages:
    36
    In Excel, can I use an IF statement to change the background color of a cell?
     
  2. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    You can base a conditional format of an IF statement in a formula.

    Can you attach a sample file (replace sensitive data with dummy data)?
     
  3. Niessen

    Niessen Thread Starter

    Joined:
    Jul 31, 2005
    Messages:
    36
    It's not really complicated . . . The spreadsheet is being used in an industrial situation where the operator is supposed to load a charge of material every 10 minutes (say) and I have a column (Column C) listing the times when a charge is due. When he loads the charge, he enters the weight into column D in the sheet. If the number is not entered into column D in time, I want a block of, say, 4 cells to turn RED to warn th operator to get on with it. . . so I want a column of equations that check the time, check column D and, if @NOW() is greater than the load time, the cells show RED. Hopefully, he sees the red cells, takes action, enters the weight . . . and the cells go back to normal.
     
  4. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    It would be really great if you could attach a file.

    Does the "Load Time" populate automatically, ot maually entered? What 4 cells do you want to turn red?
     
  5. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    He will need to recalculate the worksheet from time to time so formula "Now" to refresh and be as up to date as possible. But for this you will need a macro or something else, I'm not sure!
     
  6. Niessen

    Niessen Thread Starter

    Joined:
    Jul 31, 2005
    Messages:
    36
    Here is an Excel file that shows what I am looking for.
     

    Attached Files:

  7. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    You will need to add these two macros...

    Code:
    Private Sub Workbook_Open()
    
    Application.OnTime Now + TimeValue("00:00:05"), "Recalculate"
    
    End Sub
    
    Code:
    Public dTime As Date
    
    Sub Recalculate()
    
        dTime = Now + TimeValue("00:00:05")
        
        Application.OnTime dTime, "Recalculate"
    
        Calculate
    End Sub
    
    This will recalculate the sheet every 5 seconds.
     
  8. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Why are you turning the Range(H2:I7) red if the operator has not entered a material weight? Why not turn the Material Weight cell red?
     
  9. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I forgot to mention that in some cell you will need to add

    =Now()

    This will display the current time.
     
  10. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Change that to...

    Code:
    =Now() - Today()
    
    This will insert the current time only (exclude the date).
     
  11. Niessen

    Niessen Thread Starter

    Joined:
    Jul 31, 2005
    Messages:
    36
    The operators are often working at some distance from the computer screen where this spreadsheet is running. The block of cells I want to turn RED is large enough that they can see it from afar and know that they need to take action.

    As a side (but critical) issue, this would be my first experiment with macros. So far, I am having significant problems in getting the darn things in. Is there a good tutorial to guide me in this new task?

    Also, I added a column on the leftmost border that has a value of zero if a weight appears in the Material Weight column and 1, 2, etc. if there is no value (they haven't loaded yet). Then, with a couple of VLOOKUPs, I can find the row where the 1 sits (the next load time), and find the hour and minute to drop into a TIME function which, minus NOW(), tells me that they are late. An IF with this becomes the formula to trigger the format change to turn the block of cells red.
     
  12. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I will be more than happy to help you add the macros. What version f Excel are you running?

    Can you attach your updated version?
     
  13. Niessen

    Niessen Thread Starter

    Joined:
    Jul 31, 2005
    Messages:
    36
    Here is the file . . . both with the column 1 and an example (in Column L) of a trigger.

    I am using Excel from Office 2007.


    Note that I have changed the file format to one that "tolerates" macros.
     
  14. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Take a look at the atatched workbook.

    Here is what I have done...

    1. Cell F3 has the =Now()-Today() formula to display current time
    2. Inserted a new Column (New column is now Column J)
    3. Insert a formula into new Column J, filled from J15 thru J25
    4. Placed conditional formatting on the Range H2:I7 with a formula(=COUNTIF($J$15:$J$25,1)
    5. The macros have been added

    We can always hide the new Column J if you like.
     

    Attached Files:

  15. Niessen

    Niessen Thread Starter

    Joined:
    Jul 31, 2005
    Messages:
    36
    The file didn't go. I will try again.
     
  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/804733

  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