conditional format with cell and wildcard

[rjmdc]

hi
how would i conditionally format fill an entire row if column E contains "partial"
same if column J contains "deductions"

 

[etaf]

various ways
countif()
Match()

select the range you want to highlight - lets say A2 to D100
Now as you started at row 2 - then the formula in conditional formatting needs to start at row 2

=MATCH("*PARTIAL*",$E2,0)
=COUNTIF($E2,"*PARTIAL*")
the * is a wildcard

Same with J - BUT do you mean BOTH Eand J contains the words ?
SO E2 and J2

=COUNTIFS($E2,"*PARTIAL*",$J2,"*DEDUCTIONS*")
both have to exist

for 2007, 2010 , 2013 or 2016 excel version
Conditional Formatting

Highlight applicable range >>

A2:D100


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=COUNTIFS($E2,"*PARTIAL*",$J2,"*DEDUCTIONS*")


FormatÖ [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[rjmdc]

sorry i meant:
e has partial format row fill blue
j has deductions fill row green
table begins headers row2 and keeps growing

 

[etaf]

ok, so two rules
BUT what colour if partial is in say E2 AND J2 has deductions

 

[rjmdc]

red lets say i never thought of that possibility

 

[etaf]

Book4
	A	B	C	D	E	F	G	H	I	J	K	L
1
2					fred
3					dic					deductions
4					harry
5					h partial b
6					c partial f
7					harry
8					john
9					partial					fred
10										dic
11										harry
12										h deductions b
13										c partial f
14										deductions
15										john
16
17
18
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:L17	Expression	=COUNTIF($J2,"*Deductions*")	text	NO
A2:L17	Expression	=COUNTIF($E2,"*Partial*")	text	NO

 

[rjmdc]

do i select the entire table

 

[etaf]

Added the RED
BUT the order of the rules are import , red first and then stop if true

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
2					fred
3					dic					deductions
4					harry
5					h partial b
6					c partial f					d d deductions f f
7					harry
8					john
9					partial					fred
10										dic
11										harry
12										h deductions b
13										c partial f
14										deductions
15					partial					deductions
16
17
18
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:L17	Expression	=COUNTIFS($E2, "*Partial*",$J2,"*Deductions*")	text	YES
A2:L17	Expression	=COUNTIF($J2,"*Deductions*")	text	YES
A2:L17	Expression	=COUNTIF($E2,"*Partial*")	text	YES

 

[etaf]

you select the area you want to format
so if a table Yes the entire area
what range is the table in ?

 

[rjmdc]

do i select tyhe entire table and then add the conditional formatting?