Conditional Formatting…

[How_Do_I]

Can anyone sort this one out for me please…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
My area of interest is H18:I25 however, I’ve drawn three tables to show how I want H18:I25 to look when I18 = “W” or “D” or “L”…
<o> </o>
The values in F20:F25 will never change so I can use those to start me off… To get the CF for “6 Points Games” I could use =AND($I$18="W",$F$20=6) but what is the easiest way to build my formula please so that it works for all my conditions?

Excel Workbook
	F	G	H	I	J	K	L	M	N	O
18			Away Result	W		Away Result	D		Away Result	L
19
20	6		6 Point Games	54		6 Point Games	54		6 Point Games	54
21	4		4 Point Games	64		4 Point Games	64		4 Point Games	64
22	3		3 Point Games	88		3 Point Games	88		3 Point Games	88
23	2		2 Point Games	32		2 Point Games	32		2 Point Games	32
24	1		1 Point Games	63		1 Point Games	63		1 Point Games	63
25	0		0 Point Games	54		0 Point Games	54		0 Point Games	54
26
Sheet1

 

[Andrew Poulsom]

Try:

=AND($I$18="W",OR($F20=6,$F20=4,$F20=3))

=AND($I$18="D",OR($F20=4,$F20=2,$F20=1))

=AND($I$18="L",OR($F20=3,$F20=1,$F20=0))

 

[Matty]

Try the following CF formula:

=OR(AND(I$18="W",OR(F20=6,F20=4,F20=3)),AND(I$18="D",OR(F20=4,F20=2,F20=1)),AND(I$18="L",OR(F20=3,F20=1,F20=0)))

Matty

 

[jasonb75]

As far as I can see, the order of things in your table should never change, so you will always highlight the same rows based on W / L / D result.

The easiest way is probably to use a simple formula specific to each row of the table.

<TABLE style="WIDTH: 182pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=242><COLGROUP><COL style="WIDTH: 182pt; mso-width-source: userset; mso-width-alt: 4425" width=242><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 182pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=242>=$I$18="W"

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>=OR($I$18="W",$I$18="D")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>=OR($I$18="W",$I$18="L")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>=$I$18="D"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>=OR($I$18="D",$I$18="L")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>=$I$18="L"</TD></TR></TBODY></TABLE>

 

[jasonb75]

Obviously Andrew and Matty are more awake than me, I had things in the wrong order when I was trying the method they suggested and couldn't get it to work.

They both missed something simple though

=OR(AND(I$18="W",F20 > =3),AND(I$18="D",OR(F20=4,F20=2,F20=1)),AND(I$18="L",OR(F20=3,F20 < =1)))

 

[How_Do_I]

Thanks everyone... some good tips there...