Conditional Formatting

[bjohnstn]

Hi, I'm looking to highlight a row from 1 value to the next value.

Below is what I'd like to happen. I basically need to see when certain jobs are On and would like the time range that they are on to be highlighted green. Until I enter an OFF, in which it were to turn red.

Any help is appreciated.

2018-01-30_10-53-44.jpg

[Special-K]

Can you post a file, desensitise it if necessary.

[bjohnstn]

I think it's attached

[Special-K]

1. So the grid should be unaffected until an "On" is encountered then that cell and every cell on that row onwards should be green until an Off is encountered. The off cell should be red but everything behind that Off cell on that row should be blank ?

2. Will there only ever be one occurrance of "On..." followed by "off" on each row?

[bjohnstn]

Correct, please

[Special-K]

This should work

Condtional Formatting, 2 formulas required

Clear out any existing colours
Select E3:BL13

First formula
=AND(SUMPRODUCT(--(LEFT($E3:E3,2)="On"))=1,SUMPRODUCT(--(LEFT($E3:E3,3)="Off"))=0)
format as green
This formula essentially says given a cell in the range E3:BL13
colour this cell green ONLY if there is an "On" to the left of this cell but there is no "Off" to go with it.
(If there is an "On" and an "Off" then we are past the range to be coloured so no colour change.

Second formula
=(F5="Off")
format as red

If you have multiple On Offs on a row for each year then adjust the formula for where the year starts and ends, e.g. 2017 columns E to P, 2018 columns Q to AB etc

[bjohnstn]

it worked other than the red, so i changed it around a bit. thank you very much for the help!!