Conditional formatting query

[Whittick88]

Hi, I'm trying to add a rule to a whole column to highlight all cells between (inclusive) a start word and a stop word, heres the scenario:

1 log entry per row on say column A, if a cell contains the word 'stop' highlighting begins and each cell below is highted untill a cell with the word 'start' is reached.

Firstly is this possible, and if it is would anyone care to point me in the right direction?

Thanks!

Ben

 

[AlphaFrog]

Select column A and put this as the CF formula.

Formula is

=OR(AND(COUNTIF($A$1:A1,"*stop*")>0,COUNTIF($A$1:A1,"*start*")=0),ISNUMBER(SEARCH("start",A1)))

 

[Whittick88]

Hi, thanks for the code I'm struggling to get it to function properly though it's definitly doing something!

Currently its highlighting cells which seem a bit random, heres the actual formula as implemented in my spreadsheet:

=OR(AND(COUNTIF($I$10:I65530,"*ON HOLD*")>0,COUNTIF($I$10:I65530,"*OFF HOLD*")=0),ISNUMBER(SEARCH("ON HOLD*",I3)))

so its working on column I rather than A and the start word for highlighting is "on hold" and the stop word is "off hold".

Also this sequence occurs more than once in the affected area, so it might go on and off hold say 5 times.

Any further help would be much appreciated,

Thanks

Edit: Think this is probably due to that I3 on the end as I assume it needs to be the current cell, which it doesn't seem to do.

 

[AlphaFrog]

Select cells I10 to I? It could be slow if you go all the way down to I65536

Use this formula...
=OR(AND(COUNTIF($I$10:I10,"*ON HOLD*")>0,COUNTIF($I$10:I10,"*OFF HOLD*")=0),ISNUMBER(SEARCH("OFF HOLD",I10)))

The blue I10s have to be the 1st cell in your selection when you set the CF formula. The formula will change as it progresses down the column.

 

[Whittick88]

Aha I see, sorry was changing $I10:I10 to the range of data. Its almost working now...

Works perfectly for the first on hold -> off hold, but after that only highlights the off holds ignoring any On holds or entries inbetween.

any idea why that might be? Cheers

 

[AlphaFrog]

Works perfectly for the first on hold -> off hold, but after that only highlights the off holds ignoring any On holds or entries in between.

It does what you had requested. Nothing more.

This formula should handle multiple On Hold\Off Hold as long as they alternate. If you have two On Holds before another Off Hold then it will not work correctly.

=OR(COUNTIF($I$10:I10,"*ON HOLD*")>COUNTIF($I$10:I10,"*OFF HOLD*"),ISNUMBER(SEARCH("Off HOLD",I10)))

 

[Whittick88]

That'll be my poor attempt at explaining the problem heh!

Working perfectly now, thanks!