Modify Conditional Formatting to include Odd and Even rows

[gtd526]

Hello,

Looking to modify the Conditional Formatting for the Rule containing: =COUNTIF($H$69:$H$75,$A4)*($B4>0) and =COUNTIF($C$69:$C$75,$A4)*($B4>0), Both Applies to: A4:A33
I want to 1. Match ODD row cell values ($A$4:$A$33) to All cell values in $H$69:$H$75. 2. Match EVEN row cell values ($A$4:$A$33) to All cell values in $C$69:$C$75.
I want to keep the $B4>0 active in Both rules.

Here is a copy.

NBA.xlsm
	A	B	C					H	I	J
4	GSW	7.5
5	IND	-7.5
6	MIL	-5.5
7	ORL	5.5
8	NOP	4
9	PHI	-4
10	SAC	10
11	BOS	-10
12	OKC	-3.5
13	NYK	3.5
14	WAS	11.5
15	CHI	-11.5
16	BRK	15
17	DEN	-15
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34	Injuries	Fav w/>0 ATS						Indicator = Q>55%, AL & AM>60%
35		ATS<-10						Highlights for Home Only	Odd Ball Team
36								Fn + F2 = Cond Form to move cursor
37										$0.00
38									Odd Ball Stats
39	Max %
40	S, T>0, AL:AM>=60%		$Q$63
41	62.5%
42	2nd Max %
43	M,N>0,Q,R>0,AL:AM>=60%		$T$75
44	61.9%
45	3rd Max %
46	Q, R, S, T>0, AL:AM>=60%		$K$39
47	60.9%							Total Games	0
48								Win's Needed
49	Max $		Address					Remaining Games	0
50	S, T>0, AL:AM>=60%		$Q$63					Remaining W's Needed
51	$23.25							Win's	0
52	2nd Max $							Inj Top2 Players Teams
53	ATS <= -10		$N$75					Count = 0
54	$22.13							Teams w/ "Top 2 Player(s)"
55	3rd Max $							GSW	1
56	M,N>0,Q,R>0,AL:AM>=60%		$T$75					ORL	2
57	$19.15							BRK	3
58									4
59									5
60	Possible Candidates for Odd Ball								6
61	@ Home, B, Q, R, AL:AM, AR								7
62									8
63									9
64									10
65									11
66
67
68	Home >0, >75%, <>100%							Away >0, >75%, <>100%
69	6-2	75%	DET					BRK	72.2%	13-5
70	10-4	71%	POR					DEN	71.4%	5-2
71	11-4	73%	TOR					GSW	70.0%	7-3
72								MIN	80.0%	4-1
73
74
75
Favs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H69:J75	Cell	contains a blank value	text	NO
H69:J75	Expression	=ISTEXT($H69)	text	NO
A69:C75	Cell	contains a blank value	text	NO
A69:C75	Expression	=ISTEXT($C69)	text	NO
A4:A33	Expression	=COUNTIF($H$69:$H$75,$A4)*($B4>0)	text	NO
A4:A33	Expression	=COUNTIF($C$69:$C$75,$A4)*($B4>0)	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$Q$75	text	NO
I36:J36	Cell	does not contain a blank value	text	NO
H4:H33	Expression	=$H4=$AC$51	text	NO
H4:H33	Expression	=$H4=$Q$51	text	NO
H4:H33	Expression	=$H4=$K$39	text	NO
H4:H33	Expression	=$H4=$N$39	text	NO
H4:H33	Expression	=$H4=$Q$39	text	NO
H4:H33	Expression	=$H4=$T$39	text	NO
H4:H33	Expression	=$H4=$W$39	text	NO
H4:H33	Expression	=$H4=$T$51	text	NO
H4:H33	Expression	=$H4=$AF$39	text	NO
H4,H6:H33	Cell	contains a blank value	text	NO
H55:H65	Expression	=$H55=""	text	NO
C4:C33	Cell Value	="psh"	text	NO
C4:C33	Cell Value	="W"	text	NO
C4:C33	Cell Value	="L"	text	NO
I4:J33	Expression	=LEN($I4)>1	text	NO
H55:H65	Expression	=ISTEXT($H55)	text	NO
B4:B33	Cell Value	<0	text	NO
B4:B33	Expression	=AND($A4=$AQ4,$B4>0,$A4<>"")	text	NO
B4:B33	Expression	=B4<=-10	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$T$75	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$Q$63	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$AC$39	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$K$39	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$N$39	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$Q$39	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$T$39	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$W$39	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$Z$39	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$T$51	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$W$51	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$Z$51	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$AC$51	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$T$63	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$W$63	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$Z$63	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$AC$63	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$AC$75	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$W$87	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$N$87	text	NO
A50,A53,A56,A40,A43,A46	Cell Value	=$U$100	text	NO
A62:A67,C62:C67	Expression	=COUNTIF($AE$104:$AH$114,IF(LEN(A62),A62))=COLUMNS($AE$104:$AH$114)	text	NO
A4:A33	Expression	=COUNTIF($H$55:$H$69,$A4)	text	NO

 

[coulombevin]

Hi gtd526,

Im not quite sure to understand were does the row color is supposed to go in all that colorfull sheet. Here's a way to add a color to odd and even rows using new formula>custom formula

=ISEVEN(ROW())

=ISODD(ROW())

the in the manager, select conditional formating for this sheet (not for actual selection) and move those contition to the bottom with the arrows. That way you'll have row colors under your previous formating. Also, make sure that the conditional formating is applied to =$1:$1048576 or $A:$J.

If you need to meet multiple condition at once, use this:

=AND(ISEVEN(ROW()), another condition, another condition, ...)

=AND(ISODD(ROW()), another condition, another condition, ...)

bests regards,

Vincent

 

[Peter_SSs]

If I have understood correctly, add these to the end of your current conditions
=COUNTIF($H$69:$H$75,$A4)*($B4>0)*ISODD(ROW($A4))
=COUNTIF($C$69:$C$75,$A4)*($B4>0)*ISEVEN(ROW($A4))

 

[gtd526]

If I have understood correctly, add these to the end of your current conditions
=COUNTIF($H$69:$H$75,$A4)*($B4>0)*ISODD(ROW($A4))
=COUNTIF($C$69:$C$75,$A4)*($B4>0)*ISEVEN(ROW($A4))

You are correct, and it works. Thank you.

 

[Peter_SSs]

Cheers. Thanks for the confirmation.