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>

Name	File	Illustration	Technical	Photo
abc	BC SB2 C08	checked	checked
abc	BC SB2 C10		WIP	checked
cde	BC SB3 C10	checked	checked	checked
cde	BC SB3 C11	sent	checked
efg	BC SB2 C09	WIP	sent	WIP
efg	BC SB2 C11	checked	WIP
efg	BC SB2 C12		WIP
efg	BC RT3 C01		WIP
abc	ON SB5 C12		checked
abc	ON RT6 C06	checked	checked	checked
cde	ON SB6 C06	checked
cde	ON SB6 C07	WIP	checked
efg	ON SB6 C12	WIP	WIP
efg	ON SB6 C13	WIP	WIP
efg	ON SB6 C09	WIP	checked
efg	ON EN5 C08	sent	checked	checked
cde	ON SB5 C09	checked	WIP
cde	ON SB5 C10		WIP
cde	ON SB6 C03			checked
cde	ON SB6 C08		WIP	checked

<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

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.