Conditional formatting query

Whittick88

New Member
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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Select column A and put this as the CF formula.

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

Last edited:
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.

Last edited:
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.

Last edited:
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

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.

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

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

Working perfectly now, thanks!

Replies
5
Views
288
Replies
3
Views
695
Replies
5
Views
285
Replies
4
Views
653
Replies
2
Views
915

1,203,625
Messages
6,056,398
Members
444,862
Latest member
more_resource23

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.

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