# Solved: Excel cell color change using an IF statement



## Niessen (Jul 31, 2005)

In Excel, can I use an IF statement to change the background color of a cell?


----------



## computerman29642 (Dec 4, 2007)

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)?


----------



## Niessen (Jul 31, 2005)

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.


----------



## computerman29642 (Dec 4, 2007)

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?


----------



## Aj_old (Sep 24, 2007)

Niessen said:


> 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.


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!


----------



## Niessen (Jul 31, 2005)

Here is an Excel file that shows what I am looking for.


----------



## computerman29642 (Dec 4, 2007)

You will need to add these two macros...


```
Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:00:05"), "Recalculate"

End Sub
```


```
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.


----------



## computerman29642 (Dec 4, 2007)

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?


----------



## computerman29642 (Dec 4, 2007)

I forgot to mention that in some cell you will need to add

=Now()

This will display the current time.


----------



## computerman29642 (Dec 4, 2007)

Change that to...


```
=Now() - Today()
```
This will insert the current time only (exclude the date).


----------



## Niessen (Jul 31, 2005)

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.


----------



## computerman29642 (Dec 4, 2007)

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?


----------



## Niessen (Jul 31, 2005)

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.


----------



## computerman29642 (Dec 4, 2007)

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.


----------



## Niessen (Jul 31, 2005)

The file didn't go. I will try again.


----------



## Niessen (Jul 31, 2005)

Is it possible that I am forbidden to attach excel files that contain macros?


----------



## computerman29642 (Dec 4, 2007)

I do not believe so. The file I just attached contains a macro.

Do you get an error message when trying to attach the file?


----------



## computerman29642 (Dec 4, 2007)

The formula in the J Column needs to be changed to...


```
=IF(AND(G15="",$F$3>TIME(D15,E15,0)),1,"")
```


----------



## Niessen (Jul 31, 2005)

Looks good . . . but if you change the load time hour to, say, 17 (5PM) the red should go out (since we have not yet exceeded the indicated loading time in Columns D & E.


----------



## computerman29642 (Dec 4, 2007)

*To Create A Macro*

1. Click the *Developers Tab*
2. Click *Visual Basic*
3. Left side of the screen -> Doubl-click *ThisWorkbook*
4. Copy and paste the code

```
[B]Option Explicit

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:00:05"), "Recalculate"

End Sub[/B]
```
5. Click the *Insert Menu*
6. Click *Module*
7. Copy and Paste the code

```
[B]Option Explicit

Public dTime As Date

Sub Recalculate()

    dTime = Now + TimeValue("00:00:05")
    
    Application.OnTime dTime, "Recalculate"

    Calculate
End Sub[/B]
```
8. Click the X top right of the screen

You may also want to check the *Macro Security*

1. Click the *Developers Tab*
2. Click *Macro Security*
3. Select *Enable all macros*
4. Click *OK*


----------



## computerman29642 (Dec 4, 2007)

Niessen said:


> Looks good . . . but if you change the load time hour to, say, 17 (5PM) the red should go out (since we have not yet exceeded the indicated loading time in Columns D & E.


Did you update the formula in Column J as I indicated in post #18?


----------



## computerman29642 (Dec 4, 2007)

Take a look at the attached workbook. The formula in Column J has been updated.


----------



## Niessen (Jul 31, 2005)

FANTASTIC !

I "buggered" the starting times to get it to trigger in one minute and, not surprisingly (given your title as a "Distinguished Member") it did!

Thank you so very much. 

Now I have to figure out how and what to transfer to the "real" spreadsheet. . . . But I have a GREAT template.


----------



## computerman29642 (Dec 4, 2007)

You are very welcome.  Good Luck! :up:


----------

