Formula for conditional formatting

taywl

New Member
Joined
Feb 2, 2012
Messages
14
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,752
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 2, 2012
Messages
14
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-ignore:padding; 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>
NameFileIllustrationTechnicalPhoto
abcBC SB2 C08checkedchecked
abcBC SB2 C10WIPchecked
cdeBC SB3 C10checkedcheckedchecked
cdeBC SB3 C11sentchecked
efgBC SB2 C09WIPsentWIP
efgBC SB2 C11checkedWIP
efgBC SB2 C12WIP
efgBC RT3 C01WIP
abcON SB5 C12 checked
abcON RT6 C06checkedcheckedchecked
cdeON SB6 C06checked
cdeON SB6 C07WIPchecked
efgON SB6 C12WIPWIP
efgON SB6 C13WIPWIP
efgON SB6 C09WIPchecked
efgON EN5 C08sentcheckedchecked
cdeON SB5 C09checkedWIP
cdeON SB5 C10WIP
cdeON SB6 C03 checked
cdeON SB6 C08WIPchecked

<!--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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,752
Office Version
365
Platform
Windows
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
ABCDE
1NameFileIllustrationTechnicalPhoto
2abcBC SB2 C08checkedchecked
3abcBC SB2 C10WIPchecked
4cdeBC SB3 C10checkedcheckedchecked
5cdeBC SB3 C11sentchecked
6efgBC SB2 C09WIPsentWIP
7efgBC SB2 C11checkedWIP
8efgBC SB2 C12WIP
9efgBC RT3 C01WIP
10abcON SB5 C12checked
11abcON RT6 C06checkedcheckedchecked
12cdeON SB6 C06checked
13cdeON SB6 C07WIPchecked
14efgON SB6 C12WIPWIP
15efgON SB6 C13WIPWIP
16efgON SB6 C09WIPchecked
17efgON EN5 C08sentcheckedchecked
18cdeON SB5 C09checkedWIP
19cdeON SB5 C10WIP
20cdeON SB6 C03checked
21cdeON SB6 C08WIPchecked
CF Row
#VALUE!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,752
Office Version
365
Platform
Windows
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. :)
 

Forum statistics

Threads
1,086,105
Messages
5,387,861
Members
402,084
Latest member
Nittanyblu

Some videos you may like

This Week's Hot Topics

Top