# Conditional Formatting with Criteria



## gtd526 (Dec 6, 2022)

Hello,
I want to add Conditional Formatting, Highlight AH:AM if greater than the cell above or below. These are NFL teams facing each other (2 lines per game, 4,5--6,7--8,9--10,11 thru Row 35.

A:A and AW:AW have to match, then highlight AH:AM if ALL are greater than the other.
It should work for BUF, DAL(even if there's a Tie), DET, PHI, BAL, MIA, SEA, KC

Right know its highlighting yellow if its greater than the other.

Thank you.

NFL.xlsmAAHAIAJAKALAMAW1Week:Points2TeamsNY/APts/DPts/GSc %3D ConversionANY/AMy 
Fav31516171819204LVR5.92.3245040%5.5LVR5LAR5.41.791835.944%4.3 6NYJ5.51.671932.637%5.0 7BUF6.72.632847.556%7.0BUF8CLE62.462646.345%5.9 9CIN5.91.882337.547%5.8CIN10HOU5.41.521831.327%4.9 11DAL6.11.61835.630%6.4DAL12MIN61.982238.631%5.6 13DET6.92.883545.838%7.5DET14PHI8.22.44294043%8.1PHI15NYG4.61.691935.632%4.0 16BAL72.513042.240%7.3BAL17PIT5.31.431830.437%3.9 18JAX6.52.232640.939%7.0JAX19TEN6.61.79193138%6.3 20MIA7.92.422442.142%7.9MIA21LAC7.22.092336.441%7.8 22TB6.21.652034.835%6.7TB23SF6.61.451827.337%6.3 24CAR5.21.472031.126%4.8 25SEA7.12.382444.155%7.0SEA26KC72.93325051%7.9KC27DEN6.31.51631.835%6.6 28NE6.71.621828.639%5.4NE29ARZ5.21.982236.631%5.2 30       31       32       33       34       35       Sheet1Cell FormulasRangeFormulaAH4:AH35AH4=IFERROR(VLOOKUP($A4,Stats!$Q$3:$AL$34,22,0),"")AI4:AI35AI4=IFERROR(VLOOKUP($A4,Stats!$BZ$3:$CL$44,13,0),"")AJ4:AJ35AJ4=IFERROR(VLOOKUP($A4,Stats!$AR$3:$BN$34,22,0),"")AK4:AK35AK4=IFERROR(VLOOKUP($A4,Stats!$BZ$3:$CF$100,7,0),"")AL4:AL35AL4=IFERROR(VLOOKUP($A4,Stats!$EE$3:$EK$44,7,0),"")AM4:AM35AM4=IFERROR(VLOOKUP($A4,Stats!$Q$3:$AM$34,23,0),"")AW4,AW34,AW32,AW30,AW28,AW26,AW24,AW22,AW20,AW18,AW16,AW14,AW12,AW10,AW8,AW6AW4=IFERROR(IFS($AV4>$AV5,$A4,$A4<>"",IF($AV4=$AV5,"Tie","")),"")AW5,AW35,AW33,AW31,AW29,AW27,AW25,AW23,AW21,AW19,AW17,AW15,AW13,AW11,AW9,AW7AW5=IFERROR(IFS($AV5>$AV4,$A5,$A5<>"",IF($AV4=$AV5,"Tie","")),"")


----------



## HongRu (Dec 6, 2022)

step1. select cells(AH4:AM4), add Conditional Formatting below, and set the format you need

```
=AH4>AH5
```

step2.  select cells(AH5:AM5), add Conditional Formatting below, and set the format you need

```
=AH5>AH4
```

step3. select cells(AH4:AM5) and copy to cells(AH6:AM29)
Done.

活頁簿2AAHAIAJAKALAMAW1Week:Points2TeamsNY/APts/DPts/GSc %3D ConversionANY/AMy 
Fav31516171819204LVR5.92.3245040%5.5LVR5LAR5.41.791835.944%4.36NYJ5.51.671932.637%57BUF6.72.632847.556%7BUF8CLE62.462646.345%5.99CIN5.91.882337.547%5.8CIN10HOU5.41.521831.327%4.911DAL6.11.61835.630%6.4DAL12MIN61.982238.631%5.613DET6.92.883545.838%7.5DET14PHI8.22.44294043%8.1PHI15NYG4.61.691935.632%416BAL72.513042.240%7.3BAL17PIT5.31.431830.437%3.918JAX6.52.232640.939%7JAX19TEN6.61.79193138%6.320MIA7.92.422442.142%7.9MIA21LAC7.22.092336.441%7.822TB6.21.652034.835%6.7TB23SF6.61.451827.337%6.324CAR5.21.472031.126%4.825SEA7.12.382444.155%7SEA26KC72.93325051%7.9KC27DEN6.31.51631.835%6.628NE6.71.621828.639%5.4NE29ARZ5.21.982236.631%5.2sheet1Cells with Conditional FormattingCellConditionCell FormatStop If TrueAH7:AM7,AH9:AM9,AH11:AM11,AH13:AM13,AH15:AM15,AH17:AM17,AH19:AM19,AH21:AM21,AH23:AM23,AH25:AM25,AH27:AM27,AH29:AM29Expression=AH7>AH6textNOAH6:AM6,AH8:AM8,AH10:AM10,AH12:AM12,AH14:AM14,AH16:AM16,AH18:AM18,AH20:AM20,AH22:AM22,AH24:AM24,AH26:AM26,AH28:AM28Expression=AH6>AH7textNOAH5:AM5Expression=AH5>AH4textNOAH4:AM4Expression=AH4>AH5textNO


----------



## gtd526 (Dec 7, 2022)

HongRu said:


> step1. select cells(AH4:AM4), add Conditional Formatting below, and set the format you need
> 
> ```
> =AH4>AH5
> ...


I've added your conditional formatting, but it only changes the colors I've chosen.
Is there a way to ONLY highlight if AH:AM are ALL greater than the alternate cell?
It should work for BUF, DET, PHI, BAL, MIA, SEA, KC

NFL.xlsmAAHAIAJAKALAMAW1Week:Points2TeamsNY/APts/DPts/GSc %3D ConversionANY/AMy 
Fav31516171819204LVR5.92.3245040%5.5LVR5LAR5.41.791835.944%4.3 6NYJ5.51.671932.637%5.0 7BUF6.72.632847.556%7.0BUF8CLE62.462646.345%5.9 9CIN5.91.882337.547%5.8CIN10HOU5.41.521831.327%4.9 11DAL6.11.61835.630%6.4DAL12MIN61.982238.631%5.6 13DET6.92.883545.838%7.5DET14PHI8.22.44294043%8.1PHI15NYG4.61.691935.632%4.0 16BAL72.513042.240%7.3BAL17PIT5.31.431830.437%3.9 18JAX6.52.232640.939%7.0JAX19TEN6.61.79193138%6.3 20MIA7.92.422442.142%7.9MIA21LAC7.22.092336.441%7.8 22TB6.21.652034.835%6.7TB23SF6.61.451827.337%6.3 24CAR5.21.472031.126%4.8 25SEA7.12.382444.155%7.0SEA26KC72.93325051%7.9KC27DEN6.31.51631.835%6.6 28NE6.71.621828.639%5.4NE29ARZ5.21.982236.631%5.2 30       31       32       33       34       35       Sheet1Cells with Conditional FormattingCellConditionCell FormatStop If TrueAH7:AM7,AH9:AM9,AH11:AM11,AH13:AM13,AH15:AM15,AH17:AM17,AH19:AM19,AH21:AM21,AH23:AM23,AH25:AM25,AH27:AM27,AH29:AM29,AH31:AM31,AH33:AM33,AH35:AM35Expression=AH7>AH6textNOAH6:AM6,AH8:AM8,AH10:AM10,AH12:AM12,AH14:AM14,AH16:AM16,AH18:AM18,AH20:AM20,AH22:AM22,AH24:AM24,AH26:AM26,AH28:AM28,AH30:AM30,AH32:AM32,AH34:AM34Expression=AH6>AH7textNOAH5:AM5Expression=AH5>AH4textNOAH4:AM4Expression=AH4>AH5textNOA3Celldoes not contain a blank value textNO


----------



## HongRu (Dec 7, 2022)

try to replace 
AH7>AH6 by SUMPRODUCT((($AH4:$AM4)>($AH5:$AM5))^1)=6
and
AH6>AH7 by SUMPRODUCT((($AH4:$AM4)<($AH5:$AM5))^1)=6

NFL score highlight.xlsxAAHAIAJAKALAMAW1Week:Points2TeamsNY/APts/DPts/GSc %3D ConversionANY/AMy 
Fav31516171819204LVR5.92.3245040%5.5LVR5LAR5.41.791835.944%4.36NYJ5.51.671932.637%57BUF6.72.632847.556%7BUF8CLE62.462646.345%5.99CIN5.91.882337.547%5.8CIN10HOU5.41.521831.327%4.911DAL6.11.61835.630%6.4DAL12MIN61.982238.631%5.613DET6.92.883545.838%7.5DET14PHI8.22.44294043%8.1PHI15NYG4.61.691935.632%416BAL72.513042.240%7.3BAL17PIT5.31.431830.437%3.918JAX6.52.232640.939%7JAX19TEN6.61.79193138%6.320MIA7.92.422442.142%7.9MIA21LAC7.22.092336.441%7.822TB6.21.652034.835%6.7TB23SF6.61.451827.337%6.324CAR5.21.472031.126%4.825SEA7.12.382444.155%7SEA26KC72.93325051%7.9KC27DEN6.31.51631.835%6.628NE6.71.621828.639%5.4NE29ARZ5.21.982236.631%5.2sheet1 (2)Cells with Conditional FormattingCellConditionCell FormatStop If TrueAH7:AM7,AH9:AM9,AH11:AM11,AH13:AM13,AH15:AM15,AH17:AM17,AH19:AM19,AH21:AM21,AH23:AM23,AH25:AM25,AH27:AM27,AH29:AM29Expression=SUMPRODUCT((($AH6:$AM6)<($AH7:$AM7))^1)=6textNOAH6:AM6,AH8:AM8,AH10:AM10,AH12:AM12,AH14:AM14,AH16:AM16,AH18:AM18,AH20:AM20,AH22:AM22,AH24:AM24,AH26:AM26,AH28:AM28Expression=SUMPRODUCT((($AH6:$AM6)>($AH7:$AM7))^1)=6textNOAH5:AM5Expression=SUMPRODUCT((($AH4:$AM4)<($AH5:$AM5))^1)=6textNOAH4:AM4Expression=SUMPRODUCT((($AH4:$AM4)>($AH5:$AM5))^1)=6textNO


----------



## gtd526 (Dec 7, 2022)

HongRu said:


> try to replace
> AH7>AH6 by SUMPRODUCT((($AH4:$AM4)>($AH5:$AM5))^1)=6
> and
> AH6>AH7 by SUMPRODUCT((($AH4:$AM4)<($AH5:$AM5))^1)=6
> ...


Perfect. Thank you.


----------



## gtd526 (Dec 8, 2022)

gtd526 said:


> Perfect. Thank you.


What if I want to do the same with non-adjacent columns? O:P, Y and N > .525
NBA.xlsmNOPQRSTUVWXY2Cover2 %ATS +/-MOV2MOV2 
>ATSMOVMOVReboundsFG%FT%Asst /Asst /Pts @ Half3Mov2>ATSToverFGM450%-1.7-0.6-7.1-1.6-8.153.354%74.2%1.4457.9%52.9554%0.2-4.5 -6.6 5153%79.0%1.2854.3%54.2655%-1.3-7.7 -5.8 54.450%73.7%1.7260.8%54.5742%-2.22.4-7.10.8-8.747.657%79.6%1.7763.3%57.4840%-0.10.3 0.4 52.352%82.0%1.7854.9%58.2930%-3.3-1.0-3-1.3-3.355.551%79.3%1.6756.4%59.81036%-1-5.7 -1.0 53.453%79.9%1.7058.6%59.41167%1.54.6-3.91.1-7.452.651%77.4%1.7456.8%55.71244%0.8-3.3 -1.5 52.853%76.9%1.8659.6%55.81350%0.91.4-4.1-1.3-6.849.653%82.5%1.6759.2%55.51469%3.1-2.8 -0.7 52.653%79.2%1.7055.1%57.21573%5.99.31.81.9-5.658.853%70.4%1.6656.9%58.81670%3.10.9 4.1 50.557%79.5%1.8665.2%61.31764%1.18.12.14.8-1.257.553%72.6%1.6660.6%57.31854%0.2-4.7 -1.0 5353%79.1%1.7567.5%55.81931%-7.3-2.4-6.9-1.2-5.751.455%79.2%1.5962.7%57.52054%2.1-7.2 -7.0 5251%77.4%1.5456.2%55.62158%5.19.3-1.27.0-3.55355%79.4%1.8662.7%57.32217%-10.3-8.6 1.0 50.757%79.1%1.7669.5%602358%3.36.3-1.22.1-5.452.656%77.9%1.6963.4%60.32458%1.14.83.38.16.649.159%84.4%1.9663.4%63.42564%3.610.9 7.2 5255%80.5%2.0563.5%58.126            27            28            29            30            31            32            33            FavsCell FormulasRangeFormulaN4:N33N4=IFERROR(IF(MOD(ROW(),2)=0,ROUND(VLOOKUP($A4,TeamRankings!$BI$36:$BL$65,4,0),2),ROUND(VLOOKUP($A4,TeamRankings!$BI$3:$BL$32,4,0),2)),"")O4:O33O4=IFERROR(IF(MOD(ROW(),2)=0,ROUND(VLOOKUP($A4,TeamRankings!$BI$36:$BN$65,6,0),2),ROUND(VLOOKUP($A4,TeamRankings!$BI$3:$BN$32,6,0),2)),"")P4:P33P4=IFERROR(IF(MOD(ROW(),2)=0,ROUND(VLOOKUP($A4,TeamRankings!$BI$36:$BM$65,5,0),2),ROUND(VLOOKUP($A4,TeamRankings!$BI$3:$BM$32,5,0),2)),"")Q4:Q33,S4:S33Q4=IF(AND($B4<0,P4=""),"",IF(AND($B4<0,P4<0),$B4+P4,IF(AND($B4<0,P4>0),P4-ABS($B4),"")))R4:R33R4=IFERROR(VLOOKUP($A4,TeamRankings!$AL$3:$AP$32,5,0),"")T4:T33T4=IFERROR(VLOOKUP($A4,TeamRankings!$FN$3:$FQ$32,4,0),"")U4:U33U4=IFERROR(VLOOKUP($A4,TeamRankings!$BV$3:$BY$32,4,0),"")V4:V33V4=IFERROR(VLOOKUP($A4,TeamRankings!$ED$3:$EG$32,4,0),"")W4:W33W4=IFERROR(VLOOKUP($A4,TeamRankings!$J$3:$M$32,4,0),"")X4:X33X4=IFERROR(VLOOKUP($A4,TeamRankings!$S:$V,4,0),"")Y4:Y33Y4=IFERROR(VLOOKUP($A4,TeamRankings!$DU$3:$DX$32,4,0),"")Cells with Conditional FormattingCellConditionCell FormatStop If TrueO6:P6,O8:P8,O10:P10,O12:P12,O14:P14,O16:P16,O18:P18,O20:P20,O22:P22,O24:P24,O26:P26,O28:P28,O30:P30,O32:P32Expression=SUMPRODUCT((($O6:$P6)>($O7:$P7))^1)=2textNOO7:P7,O9:P9,O11:P11,O13:P13,O15:P15,O17:P17,O19:P19,O21:P21,O23:P23,O25:P25,O27:P27,O29:P29,O31:P31,O33:P33Expression=SUMPRODUCT((($O6:$P6)<($O7:$P7))^1)=2textNOK4:O33Expression=$A4=""textNOO4:P4Expression=SUMPRODUCT((($O4:$P4)>($O5:$P5))^1)=2textNOO5:P5Expression=SUMPRODUCT((($O4:$P4)<($O5:$P5))^1)=2textNON4:N33Expression=$N4>0.525textNOU4:U33Cellcontains a blank value textNOQ4:Q33,AG4:AG33,AI4:AK33Expression=AND($Q4<>"",$Q4>0,ISNUMBER($AI4),$AI4>=0.6,ISNUMBER($AJ4),$AJ4>=0.6,ISNUMBER($AK4),$AK4>=0.6,$AN4<=$AN$1)textNON4:N33,L4:L33,W4:X33,AI4:AK33Expression=AND($A4=$AG4,$N4<>"",$N4>=0.65,$L4<>"",$L4>=0.65,ISNUMBER($AI4),$AI4>=0.7,ISNUMBER($AJ4),$AJ4>=0.7,ISNUMBER($AK4),$AK4>=0.7,$AN4<=$AN$1)textNOQ4:Q33,S4:S33,AG4:AG33,AI4:AK33Expression=AND($Q4<>"",$Q4>0,$S4<>"",$S4>0,ISNUMBER($AI4),$AI4>=0.6,ISNUMBER($AJ4),$AJ4>=0.6,ISNUMBER($AK4),$AK4>=0.6,$AN4<=$AN$1)textNOS4:S33,AG4:AG33,AI4:AK33Expression=AND($S4<>"",$S4>0,ISNUMBER($AI4),$AI4>=0.6,ISNUMBER($AJ4),$AJ4>=0.6,ISNUMBER($AK4),$AK4>=0.6,$AN4<=$AN$1)textNOQ4:Q33,S4:S33,AG4:AG33Expression=AND($A4=$AG4,$Q4<>"",$Q4>0,$S4<>"",$S4>0,$AN4<=$AN$1)textNOS4:S33,AG4:AG33Expression=AND($S4<>"",$S4>0,$AN4<=$AN$1)textNOQ4:Q33,AG4:AG33Expression=AND($Q4<>"",$Q4>0,$AN4<=$AN$1)textNOR4:R33Expression=AND($R4>=LARGE($R$4:$R$33,3),$R4<>"")textNOW4:W33Expression=AND($W4>=1.85,$W4<>"")textNOX4:X33Expression=AND($X4>=0.65,$X4<>"")textNOT4:T33Cell Valuetop 1 valuestextNOU4:U33Cell Valuetop 1 valuestextNOV4:V33Cell Valuetop 1 valuestextNOY4:Y33Cell Valuetop 1 valuestextNOU2Expression=$AO$3="032"textNOT2Expression=$AO$3="030"textNOY2Expression=$AO$3="025"textNOT2:V2Expression=$AO$3="018"textNOL2,L3:O3Expression=$AY$3="020"textNOT2:V2Expression=$AY$3="018"textNOX3,W2:AB2,AB3,Z3Expression=$AO$3="034"textNOX3,W2:AB2,AB3,Z3Expression=$AY$3="034"textNOQ2:Q3Celldoes not contain a blank value textNOS2:S3Celldoes not contain a blank value textNOAB4:AC33,T4:Z33Expression=T4=MAX(OFFSET(T$4,FLOOR(ROW(T4)-ROW(T$4),2),0,2,1))textNOR4:R33Expression=R4=MAX(OFFSET(R$4,FLOOR(ROW(R4)-ROW(R$4),2),0,2,1))textNOT4:AC33Expression=AND(ISODD(ROW(T4)),T4=T3)textNOT4:AC33Expression=AND(ISEVEN(ROW(T4)),T4=T5)textNOM4:M33,O4:S33Cell Value>0textNOM4:M33,O4:S33Cell Value<0textNON4:N33Expression=$N4="Tie"textNO


----------



## HongRu (Dec 8, 2022)

gtd526 said:


> What if I want to do the same with non-adjacent columns? O:P, Y and N > .525


I don't really understand what you need.
Maybe you can explain more clearly.


----------



## gtd526 (Dec 8, 2022)

HongRu said:


> I don't really understand what you need.
> Maybe you can explain more clearly.


I want to highlight if all are True.
O:P and Y:Y are greater than the opposing cell (cell above or below), and N:N is greater than .525.
Its the same as the above posts (which you solved), using different columns with different criteria in N:N.
2 lines per game, 4,5--6,7--8,9--10,11 thru Row 33
I have your conditional formatting in O:P. Just need extend that to Y:Y and N:N > .525
UTA should be highlighted.

NBA.xlsmANOPQRSTUVWXYAG1212/8Cover2 %ATS +/-MOV2MOV2 
>ATSMOVMOVReboundsFG%FT%Asst /Asst /Pts @ Half3TeamMov2>ATSToverFGM4LAC46%-2.4-0.9 -1.7 53.754%74.0%1.4458.2%52.8 5MIA25%-4.50.1-4.9-1.9-6.949.452%84.0%1.6561.5%56.2MIA6HOU47%0.2-9.3-11.3-6.3-8.355.551%78.1%1.1954.8%56HOU7SAN39%-6.5-11.1 -11.8 50.552%72.2%1.7066.5%55.1 8DEN40%-5-4.1 1.0 49.957%74.7%1.8365.1%60.4 9POR50%1.52.51.5-0.7-1.751.454%76.7%1.5660.6%56POR10UTA57%2.7-1.4 2.1 52.456%78.0%1.6963.1%60.6UTA11DET55%-1.4-6.8 -7.0 51.951%77.4%1.5356.0%55.1 12             13             14             15             16             17             18             19             20             21             22             23             24             25             26             27             28             29             30             31             32             33             Sheet2Cell FormulasRangeFormulaA2A2=TODAY()N4:N33N4=IFERROR(IF(MOD(ROW(),2)=0,ROUND(VLOOKUP($A4,TeamRankings!$BI$36:$BL$65,4,0),2),ROUND(VLOOKUP($A4,TeamRankings!$BI$3:$BL$32,4,0),2)),"")O4:O33O4=IFERROR(IF(MOD(ROW(),2)=0,ROUND(VLOOKUP($A4,TeamRankings!$BI$36:$BN$65,6,0),2),ROUND(VLOOKUP($A4,TeamRankings!$BI$3:$BN$32,6,0),2)),"")P4:P33P4=IFERROR(IF(MOD(ROW(),2)=0,ROUND(VLOOKUP($A4,TeamRankings!$BI$36:$BM$65,5,0),2),ROUND(VLOOKUP($A4,TeamRankings!$BI$3:$BM$32,5,0),2)),"")Q4:Q33,S4:S33Q4=IF(AND($B4<0,P4=""),"",IF(AND($B4<0,P4<0),$B4+P4,IF(AND($B4<0,P4>0),P4-ABS($B4),"")))R4:R33R4=IFERROR(VLOOKUP($A4,TeamRankings!$AL$3:$AP$32,5,0),"")T4:T33T4=IFERROR(VLOOKUP($A4,TeamRankings!$FN$3:$FQ$32,4,0),"")U4:U33U4=IFERROR(VLOOKUP($A4,TeamRankings!$BV$3:$BY$32,4,0),"")V4:V33V4=IFERROR(VLOOKUP($A4,TeamRankings!$ED$3:$EG$32,4,0),"")W4:W33W4=IFERROR(VLOOKUP($A4,TeamRankings!$J$3:$M$32,4,0),"")X4:X33X4=IFERROR(VLOOKUP($A4,TeamRankings!$S:$V,4,0),"")Y4:Y33Y4=IFERROR(VLOOKUP($A4,TeamRankings!$DU$3:$DX$32,4,0),"")AG4,AG32,AG30,AG28,AG26,AG24,AG22,AG20,AG18,AG16,AG14,AG12,AG10,AG8,AG6AG4=IFS(AF4>AF5,$A4,AF4<AF5,"",AND(AF4=0,AF5=0),"",AF4=AF5,"Tie")AG5,AG33,AG31,AG29,AG27,AG25,AG23,AG21,AG19,AG17,AG15,AG13,AG11,AG9,AG7AG5=IFS(AF5>AF4,$A5,AF5<AF4,"",AND(AF5=0,AF4=0),"",AF5=AF4,"Tie")Cells with Conditional FormattingCellConditionCell FormatStop If TrueO6:P6,O8:P8,O10:P10,O12:P12,O14:P14,O16:P16,O18:P18,O20:P20,O22:P22,O24:P24,O26:P26,O28:P28,O30:P30,O32:P32Expression=SUMPRODUCT((($O6:$P6)>($O7:$P7))^1)=2textNOO7:P7,O9:P9,O11:P11,O13:P13,O15:P15,O17:P17,O19:P19,O21:P21,O23:P23,O25:P25,O27:P27,O29:P29,O31:P31,O33:P33Expression=SUMPRODUCT((($O6:$P6)<($O7:$P7))^1)=2textNOO4:P4Expression=SUMPRODUCT((($O4:$P4)>($O5:$P5))^1)=2textNOO5:P5Expression=SUMPRODUCT((($O4:$P4)<($O5:$P5))^1)=2textNO


----------



## HongRu (Dec 8, 2022)

Try
put the formula in N4 conditonal format

```
=AND($N4>0.525,$O4>$O5,$P4>$P5,$Y4>$Y5)
```
in N5

```
=AND($N5>0.525,$O4<$O5,$P4<$P5,$Y4<$Y5)
```
then 
copy N4:N5 format only to what you want.

I think this formula is much easier to understand and amend.
HTH


----------



## gtd526 (Dec 8, 2022)

HongRu said:


> Try
> put the formula in N4 conditonal format
> 
> ```
> ...


Everything is working well. Thank you for the formulas.


----------



## gtd526 (Dec 6, 2022)

Hello,
I want to add Conditional Formatting, Highlight AH:AM if greater than the cell above or below. These are NFL teams facing each other (2 lines per game, 4,5--6,7--8,9--10,11 thru Row 35.

A:A and AW:AW have to match, then highlight AH:AM if ALL are greater than the other.
It should work for BUF, DAL(even if there's a Tie), DET, PHI, BAL, MIA, SEA, KC

Right know its highlighting yellow if its greater than the other.

Thank you.

NFL.xlsmAAHAIAJAKALAMAW1Week:Points2TeamsNY/APts/DPts/GSc %3D ConversionANY/AMy 
Fav31516171819204LVR5.92.3245040%5.5LVR5LAR5.41.791835.944%4.3 6NYJ5.51.671932.637%5.0 7BUF6.72.632847.556%7.0BUF8CLE62.462646.345%5.9 9CIN5.91.882337.547%5.8CIN10HOU5.41.521831.327%4.9 11DAL6.11.61835.630%6.4DAL12MIN61.982238.631%5.6 13DET6.92.883545.838%7.5DET14PHI8.22.44294043%8.1PHI15NYG4.61.691935.632%4.0 16BAL72.513042.240%7.3BAL17PIT5.31.431830.437%3.9 18JAX6.52.232640.939%7.0JAX19TEN6.61.79193138%6.3 20MIA7.92.422442.142%7.9MIA21LAC7.22.092336.441%7.8 22TB6.21.652034.835%6.7TB23SF6.61.451827.337%6.3 24CAR5.21.472031.126%4.8 25SEA7.12.382444.155%7.0SEA26KC72.93325051%7.9KC27DEN6.31.51631.835%6.6 28NE6.71.621828.639%5.4NE29ARZ5.21.982236.631%5.2 30       31       32       33       34       35       Sheet1Cell FormulasRangeFormulaAH4:AH35AH4=IFERROR(VLOOKUP($A4,Stats!$Q$3:$AL$34,22,0),"")AI4:AI35AI4=IFERROR(VLOOKUP($A4,Stats!$BZ$3:$CL$44,13,0),"")AJ4:AJ35AJ4=IFERROR(VLOOKUP($A4,Stats!$AR$3:$BN$34,22,0),"")AK4:AK35AK4=IFERROR(VLOOKUP($A4,Stats!$BZ$3:$CF$100,7,0),"")AL4:AL35AL4=IFERROR(VLOOKUP($A4,Stats!$EE$3:$EK$44,7,0),"")AM4:AM35AM4=IFERROR(VLOOKUP($A4,Stats!$Q$3:$AM$34,23,0),"")AW4,AW34,AW32,AW30,AW28,AW26,AW24,AW22,AW20,AW18,AW16,AW14,AW12,AW10,AW8,AW6AW4=IFERROR(IFS($AV4>$AV5,$A4,$A4<>"",IF($AV4=$AV5,"Tie","")),"")AW5,AW35,AW33,AW31,AW29,AW27,AW25,AW23,AW21,AW19,AW17,AW15,AW13,AW11,AW9,AW7AW5=IFERROR(IFS($AV5>$AV4,$A5,$A5<>"",IF($AV4=$AV5,"Tie","")),"")


----------



## gtd526 (Saturday at 8:07 AM)

gtd526 said:


> Everything is working well. Thank you for the formulas.


Hello, this is using Conditional Formatting to highlight cells if TRUE, and its working.
How can I add to the formula if $A4 (team) is showing in another cell range (G52:G66) then return FALSE so it doesn't show highlights. 
Thank you.


----------



## HongRu (Sunday at 10:59 PM)

gtd526 said:


> Hello, this is using Conditional Formatting to highlight cells if TRUE, and its working.
> How can I add to the formula if $A4 (team) is showing in another cell range (G52:G66) then return FALSE so it doesn't show highlights.
> Thank you.


Try this.

Select AH4:AM29。
Add conditional formatting as the first Criteria
=COUNTIF($G$52:$G$66,$A4)>0
check "Stop If True" as True.

NFL score highlight-1.xlsxAAHAIAJAKALAMAW1Week:Points2TeamsNY/APts/DPts/GSc %3D ConversionANY/AMy 
Fav31516171819204LVR5.92.3245040%5.5LVR5LAR5.41.791835.944%4.36NYJ5.51.671932.637%57BUF6.72.632847.556%7BUF8CLE62.462646.345%5.99CIN5.91.882337.547%5.8CIN10HOU5.41.521831.327%4.911DAL6.11.61835.630%6.4DAL12MIN61.982238.631%5.613DET6.92.883545.838%7.5DET14PHI8.22.44294043%8.1PHI15NYG4.61.691935.632%416BAL72.513042.240%7.3BAL17PIT5.31.431830.437%3.918JAX6.52.232640.939%7JAX19TEN6.61.79193138%6.320MIA7.92.422442.142%7.9MIA21LAC7.22.092336.441%7.822TB6.21.652034.835%6.7TB23SF6.61.451827.337%6.324CAR5.21.472031.126%4.825SEA7.12.382444.155%7SEA26KC72.93325051%7.9KC27DEN6.31.51631.835%6.628NE6.71.621828.639%5.4NE29ARZ5.21.982236.631%5.2sheet1 (2)Cells with Conditional FormattingCellConditionCell FormatStop If TrueAH4:AM29Expression=COUNTIF($G$52:$G$66,$A4)>0textYESAH7:AM7,AH9:AM9,AH11:AM11,AH13:AM13,AH15:AM15,AH17:AM17,AH19:AM19,AH21:AM21,AH23:AM23,AH25:AM25,AH27:AM27,AH29:AM29Expression=SUMPRODUCT((($AH6:$AM6)<($AH7:$AM7))^1)=6textNOAH6:AM6,AH8:AM8,AH10:AM10,AH12:AM12,AH14:AM14,AH16:AM16,AH18:AM18,AH20:AM20,AH22:AM22,AH24:AM24,AH26:AM26,AH28:AM28Expression=SUMPRODUCT((($AH6:$AM6)>($AH7:$AM7))^1)=6textNOAH5:AM5Expression=SUMPRODUCT((($AH4:$AM4)<($AH5:$AM5))^1)=6textNOAH4:AM4Expression=SUMPRODUCT((($AH4:$AM4)>($AH5:$AM5))^1)=6textNO


----------



## gtd526 (Wednesday at 12:25 AM)

HongRu said:


> Try this.
> 
> Select AH4:AM29。
> Add conditional formatting as the first Criteria
> ...


Thank you.


----------

