# Make cell background color if certain condition is fullfilled

#### youneskil

##### New Member
Hey guys!

In my table you see the two columns time and delta_time.
"time" indicates timestamps of an driverless transport system (DTS) while entering a certain workstation. "delta_time" indicates the time the DTS needed at the previous workstation.
In case of an problem the DTS will stop longer at a particual workstation and potentially causes a jam in the assembly line. To analyse which DTS was originally responsible for the jam I check
where the delta_time is higher then 00:01:30 (planned cycle time) and set this as local maximum.
You see in the table that the DTS stoped at workstation 02 for 00:02:48. After that delta_time decreases. That means that workstation 02 is the responsible for the jam.
The same you find at workstation 07.

Now I need an automated mechanism for painting green the the jam responsible workstations. I hope you got what I mean :D

 ordernumber date time delta_time workstation ZSC2 16.08.2021 07:03:00 00:00:00​ 01 ZSC2 16.08.2021 07:05:48 00:02:48​ 02 ZSC2 16.08.2021 07:07:29 00:01:41​ 03 ZSC2 16.08.2021 07:09:07 00:01:38​ 04 ZSC2 16.08.2021 07:10:34 00:01:27​ 04 ZSC2 16.08.2021 07:11:59 00:01:25​ 06 ZSC2 16.08.2021 07:14:38 00:02:39​ 07 ZSC2 16.08.2021 07:16:46 00:02:08​ 08 ZSC2 16.08.2021 07:18:14 00:01:28​ 09 ZSC2 16.08.2021 07:19:46 00:01:32​ 10

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### etaf

##### Well-known Member
why isn't 1:41 , 1:38 highlighted - if over 1:30 ? 03 and 04
same for 08

highlighting over 1:30 would be simple with conditional formatting
BUT
how long before you count the next jam ? over 1:30

what if after the JAM - the next 10 workstations are all over 1:30

#### youneskil

##### New Member
Hi etaf, thanks for your response.
I do not highlight these because they are not responsible for the jam.
Rsponsible for the jam is only the one with the maximum delta_time. The following timestamps are also above 00:01:30 because they need time to solve the jam.
You see that after a maximum the delta_time decreases. When it starts increasing again that means there is another jam responsible DTS.

#### etaf

##### Well-known Member
assuming the time ALWAYS decreases towards 1:30 after a block
then
=AND(D2>TIMEVALUE("0:1:30"), D2>D1)
could be used as conditional formatting

see formula entered in G , just to give an example of the results in spreadsheet - NOT needed for conditional formatting

if you want to highlight Columns D and E

then Select those 2 columns and use a rule
=AND(\$D2>TIMEVALUE("0:1:30"), \$D2>\$D1)

is 1min 30sec actually OK otherwise you need a > =

BUT will you have different order numbers in the same list ?

I have filled and changed font, you can JUST change font

Book4
ABCDEFG
1ordernumberdatetimedelta_timeworkstationCondition
2ZSC216.08.20217:03:0000:00:001FALSE
3ZSC216.08.20217:05:4800:02:482TRUE
4ZSC216.08.20217:07:2900:01:413FALSE
5ZSC216.08.20217:09:0700:01:384FALSE
6ZSC216.08.20217:10:3400:01:274FALSE
7ZSC216.08.20217:11:5900:01:256FALSE
8ZSC216.08.20217:14:3800:02:297TRUE
9ZSC216.08.20217:16:4600:02:088FALSE
10ZSC216.08.20217:18:1400:01:289FALSE
11ZSC216.08.20217:19:4600:01:3210TRUE
Sheet1
Cell Formulas
RangeFormula
G2:G11G2=AND(D2>TIMEVALUE("0:1:30"), D2>D1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:E19Expression=AND(\$D2>TIMEVALUE("0:1:30"), \$D2>\$D1)textNO

#### youneskil

##### New Member
Hey etaf, thanks for your help!
I forgot to tell you that content of column D is already calculated by a formular that is =IF(AND(A2=A1;C2>C1);C2-C1;0). Furthermore I have timeformat in column D.
Is it still possible to integrate your formular in mine?

#### etaf

##### Well-known Member
its a conditional format formula
Did it NOT work ?

Its not entered into the cell itself, and so no need to integrate , i notice you are using ; instead of , , so replace my comma , with semicolon ;

Column G was just to show you the formula working, NOT needed at all
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
D2:E1000 , change rows to suite your range - BUT note if you start at a different row number, the formula below has to be the same row number so if you select D10:E1000 - then the formula would start at row 10 =AND(\$D10>TIMEVALUE("0:1:30") ; \$D10>\$D9)

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(\$D2>TIMEVALUE("0:1:30") ; \$D2>\$D1)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Replies
4
Views
88
Replies
2
Views
427
Replies
1
Views
253
Replies
2
Views
86
Replies
2
Views
125

1,147,735
Messages
5,742,869
Members
423,760
Latest member
photogfrog

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back