Formula for conditional formatting

[taywl]

I'd like to highlight a row based on certain criteria.

Say for example in row 7, if C7 contains the word "checked" or its empty, and if D7 contains the word "checked" or its empty, and if E7 contains the word "checked" or its empty, then i want this row to be highlighted.

Thanks in advance!

 

[Peter_SSs]

I'm assuming that you don't need all 16,000+ columns in a row highlighted and that you want to start your checks in row 2.
Select from A2 to the bottom right of your data and apply this CF formula. If it does not do what you want, please give some examples and more explanation.

=SUBSTITUTE(LOWER($C2&$D2&$E2),"checked","")=""

 

[taywl]

I've tried your formula and all cells are highlighted. I will need about 2000 rows to input my data. Here are more examples:<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl64 {color:black;}.xl65 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0;}.xl66 {background:#BDD7EE; mso-pattern:black none;}.xl67 {color:black; background:yellow; mso-pattern:black none;}.xl68 {background:yellow; mso-pattern:black none;}.xl69 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; background:yellow; mso-pattern:black none;}--></style>[TABLE="width: 325"]
<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 65"]Name[/TD]
[TD="class: xl66, width: 65"]File[/TD]
[TD="class: xl66, width: 65"]Illustration[/TD]
[TD="class: xl66, width: 65"]Technical[/TD]
[TD="class: xl66, width: 65"]Photo[/TD]
[/TR]
[TR]
[TD="class: xl67"]abc[/TD]
[TD="class: xl67"]BC SB2 C08[/TD]
[TD="class: xl68"]checked[/TD]
[TD="class: xl68"]checked[/TD]
[TD="class: xl68"] [/TD]
[/TR]
[TR]
[TD="class: xl64"]abc[/TD]
[TD="class: xl64"]BC SB2 C10[/TD]
[TD][/TD]
[TD]WIP[/TD]
[TD]checked[/TD]
[/TR]
[TR]
[TD="class: xl67"]cde[/TD]
[TD="class: xl67"]BC SB3 C10[/TD]
[TD="class: xl68"]checked[/TD]
[TD="class: xl68"]checked[/TD]
[TD="class: xl68"]checked[/TD]
[/TR]
[TR]
[TD="class: xl64"]cde[/TD]
[TD="class: xl65"]BC SB3 C11[/TD]
[TD]sent[/TD]
[TD]checked[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]efg[/TD]
[TD="class: xl64"]BC SB2 C09[/TD]
[TD]WIP[/TD]
[TD]sent[/TD]
[TD]WIP[/TD]
[/TR]
[TR]
[TD="class: xl64"]efg[/TD]
[TD="class: xl64"]BC SB2 C11[/TD]
[TD]checked[/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]efg[/TD]
[TD="class: xl64"]BC SB2 C12[/TD]
[TD][/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]efg[/TD]
[TD="class: xl64"]BC RT3 C01[/TD]
[TD][/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67"]abc[/TD]
[TD="class: xl69"]ON SB5 C12[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"]checked[/TD]
[TD="class: xl68"] [/TD]
[/TR]
[TR]
[TD="class: xl67"]abc[/TD]
[TD="class: xl67"]ON RT6 C06[/TD]
[TD="class: xl68"]checked[/TD]
[TD="class: xl68"]checked[/TD]
[TD="class: xl68"]checked[/TD]
[/TR]
[TR]
[TD="class: xl67"]cde[/TD]
[TD="class: xl67"]ON SB6 C06[/TD]
[TD="class: xl68"]checked[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[/TR]
[TR]
[TD="class: xl64"]cde[/TD]
[TD="class: xl65"]ON SB6 C07[/TD]
[TD]WIP[/TD]
[TD]checked[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]efg[/TD]
[TD="class: xl65"]ON SB6 C12[/TD]
[TD]WIP[/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]efg[/TD]
[TD="class: xl65"]ON SB6 C13[/TD]
[TD]WIP[/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]efg[/TD]
[TD="class: xl64"]ON SB6 C09[/TD]
[TD]WIP[/TD]
[TD]checked[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]efg[/TD]
[TD="class: xl64"]ON EN5 C08[/TD]
[TD]sent[/TD]
[TD]checked[/TD]
[TD]checked[/TD]
[/TR]
[TR]
[TD="class: xl64"]cde[/TD]
[TD="class: xl64"]ON SB5 C09[/TD]
[TD]checked[/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]cde[/TD]
[TD="class: xl64"]ON SB5 C10[/TD]
[TD][/TD]
[TD]WIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67"]cde[/TD]
[TD="class: xl67"]ON SB6 C03[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"]checked[/TD]
[/TR]
[TR]
[TD="class: xl64"]cde[/TD]
[TD="class: xl64"]ON SB6 C08[/TD]
[TD][/TD]
[TD]WIP[/TD]
[TD]checked[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


Notice that I only want the status for all 3 columns C, D, and E to be either "checked" or empty for the row to be highlighted.

 

[Peter_SSs]

Here is my sheet with your sample data and I followed the steps in my previous post. Is this the result you would expect?

Excel Workbook
	A	B	C	D	E
1	Name	File	Illustration	Technical	Photo
2	abc	BC SB2 C08	checked	checked
3	abc	BC SB2 C10		WIP	checked
4	cde	BC SB3 C10	checked	checked	checked
5	cde	BC SB3 C11	sent	checked
6	efg	BC SB2 C09	WIP	sent	WIP
7	efg	BC SB2 C11	checked	WIP
8	efg	BC SB2 C12		WIP
9	efg	BC RT3 C01		WIP
10	abc	ON SB5 C12		checked
11	abc	ON RT6 C06	checked	checked	checked
12	cde	ON SB6 C06	checked
13	cde	ON SB6 C07	WIP	checked
14	efg	ON SB6 C12	WIP	WIP
15	efg	ON SB6 C13	WIP	WIP
16	efg	ON SB6 C09	WIP	checked
17	efg	ON EN5 C08	sent	checked	checked
18	cde	ON SB5 C09	checked	WIP
19	cde	ON SB5 C10		WIP
20	cde	ON SB6 C03			checked
21	cde	ON SB6 C08		WIP	checked
CF Row

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =SUBSTITUTE(LOWER($C2&$D2&$E2),"checked","")=""	Abc

 

[taywl]

This works. Thanks a lot for your help!

 

[Peter_SSs]

This works. Thanks a lot for your help!

No problem. I take it that you must have not quite implemented the suggestion correctly the first time. In any case, the main thing is that it is resolved now.