Conditional Formatting: Trouble adding an additional Column qualifier value into an existing formula.

[wittonlin]

I have a live sample I can include somehow w/ Sheet 'G2-3' and Sheet 'Data'. I don't see a way to upload a file. I could upload it to my servers easy enough if needed.

From Sheet 'G2-3'
"Target Client Table"

[TABLE="class: grid, width: 450, align: left"]
<tbody>[TR]
[TD="align: center"]Col BB
[/TD]
[TD="align: center"]Col BC
[/TD]
[TD="align: center"]Col BD
[/TD]
[TD="align: center"]Col BE
[/TD]
[TD="align: center"]Col BF
[/TD]
[TD="align: center"]Col BG
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]IL[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]iiii[/TD]
[TD="align: center"]224[/TD]
[TD="align: center"]773[/TD]
[TD="align: center"]312[/TD]
[TD="align: center"]rule #1[/TD]
[/TR]
[TR]
[TD="align: center"]TX[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #2[/TD]
[/TR]
[TR]
[TD="align: center"]IN[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]iiii[/TD]
[TD="align: center"]260[/TD]
[TD="align: center"]574[/TD]
[TD="align: center"]812[/TD]
[TD="align: center"]rule #3[/TD]
[/TR]
[TR]
[TD="align: center"]OH[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #4[/TD]
[/TR]
[TR]
[TD="align: center"]OH[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]iiii[/TD]
[TD="align: center"]283[/TD]
[TD="align: center"]513[/TD]
[TD="align: center"]937[/TD]
[TD="align: center"]rule #5[/TD]
[/TR]
[TR]
[TD="align: center"]MO[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #6[/TD]
[/TR]
[TR]
[TD="align: center"]KY[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #7[/TD]
[/TR]
[TR]
[TD="align: center"]MD[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #8[/TD]
[/TR]
[TR]
[TD="align: center"]CT[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #9[/TD]
[/TR]
[TR]
[TD="align: center"]MA[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #1 0[/TD]
[/TR]
[TR]
[TD="align: center"]RI[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #1 1[/TD]
[/TR]
</tbody>[/TABLE]

​

From Sheet 'Data'
"Source Data Tables"


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Col H
[/TD]
[TD="align: center"]Col S
[/TD]
[TD="align: center"]/ZZ/
[/TD]
[TD="align: center"]Col BQ
[/TD]
[TD="align: center"]Col BR
[/TD]
[TD="align: center"]Meets Rule
[/TD]
[/TR]
[TR]
[TD="align: center"]State[/TD]
[TD="align: center"]Areacode[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]Kenny
G2-1[/TD]
[TD="align: center"]Ron
G2-3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]MI[/TD]
[TD="align: center"]906[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]N/A[/TD]
[/TR]
[TR]
[TD="align: center"]TX
[/TD]
[TD="align: center"]409[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #2 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]MO
[/TD]
[TD="align: center"]903[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #6 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]OH
[/TD]
[TD="align: center"]740[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #4 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]OH
[/TD]
[TD="align: center"]283
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #5[/TD]
[/TR]
[TR]
[TD="align: center"]OH
[/TD]
[TD="align: center"]937
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #5[/TD]
[/TR]
[TR]
[TD="align: center"]MA
[/TD]
[TD="align: center"]517[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #1 0[/TD]
[/TR]
[TR]
[TD="align: center"]TX
[/TD]
[TD="align: center"]832[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #2 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]IN
[/TD]
[TD="align: center"]574
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #3 ; Pattern = xlCrissCross[/TD]
[/TR]
[TR]
[TD="align: center"]OH
[/TD]
[TD="align: center"]614[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #4 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]MD
[/TD]
[TD="align: center"]636[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #8[/TD]
[/TR]
[TR]
[TD="align: center"]TX
[/TD]
[TD="align: center"]832[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #2 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]IN
[/TD]
[TD="align: center"]812
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #3 ; Pattern = xlCrissCross[/TD]
[/TR]
[TR]
[TD="align: center"]KY
[/TD]
[TD="align: center"]270[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #7[/TD]
[/TR]
[TR]
[TD="align: center"]CT
[/TD]
[TD="align: center"]860[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #9[/TD]
[/TR]
[TR]
[TD="align: center"]MA
[/TD]
[TD="align: center"]413[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #1 0[/TD]
[/TR]
[TR]
[TD="align: center"]IL
[/TD]
[TD="align: center"]773
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #1 ; Pattern = xlGray50[/TD]
[/TR]
[TR]
[TD="align: center"]IL
[/TD]
[TD="align: center"]773
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #1 ; Pattern = xlGray50[/TD]
[/TR]
</tbody>[/TABLE]










COLUMN BC RANKING

0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline
1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline
2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline
3 = .Pattern = xlGray16, RGB(71,255,163), Default text, Thin Outline

<tbody>
[TD="class: xl77"][/TD]

</tbody>

---

The 1st formula looks at all the Area Codes listed, per "Target Client Table" sheet 'G2-3', in Column Cells BE1:BG15 and when it matches with an Area Code in a Row from Column S on the"Source Data Tables" 'Data' Sheet Conditional Formatting is added to highlight the blank Cells in Column BR, in this example. (i.e. "Applies to" = $BR$101:$BR$250)
NOTE: A "y" or not in Column BQ/BR has no affect.

1st formula =COUNTIF(('G2-3'!$BE$1:$BO$15),$S101)

Target Sheet 'G2-3' Target Data Table 'BE3:BO15, Source Column 'S100' To Compare Data For True/False.

---

The 2nd formula looks at all the states listed, per "Target Client Table" sheet 'G2-3', in Column BB and when it matches with a state in a Row from Column H on the "Source Data Tables" 'Data' Sheet Conditional Formatting is added to highlight the blank Cells in Column BR, in this example. (i.e. "Applies to" = $BR$101:$BR$250)
NOTE: A "y" or not in Column BQ/BR has no affect.

2nd formula =COUNTIF(('G2-3'!$BB$1:$BB$15),$H101)

Target Sheet 'G2-3' Target Data Table 'BB3:BB15, Source Column 'H100' To Compare Data For True/False.

---

***I NEED TO ADD THIS CHANGE TO BOTH FORMULAS:

Need to ADD Values = {0,1,2,3,-1} from the Client Sheet 'G2-3', in "Target Client Table" Column 'BC3:BC15', into the mix.

(-1s) are highest priorities and include a STOP, because those (via either State OR Area Code) are immediately darkened out, not to be used.

The rest in this order of highest to lowest priority is {0,1,2,3}. I was going to continue to use a heavier pattern (i.e. Pattern = xlGray50, xlGray25, xlGray16) and darker background color (shades of green) and lighten BOTH are the priority lowers with 3 the lightest pattern, xlGray16, and lightest green. (I have these all calculated and yes I recognize that this isn't contiguous with the initial rule that darkens out the very lowest 'zero' priority (-1)

I just need to code to recognize the priority Column Values = {0,1,2,3,-1} from Column 'BC3:BC15' WHEN there's first a match with STATE ($BB$1:$BB$15),$H101) or AREA CODE ($BE$1:$BO$15),$S101); into the existing formulas, so I can then apply the appropriate 'weighted' formatting in the Client's "Applies to" Columns, $BR$101:$BR$250.

 

[Special-K99]

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[wittonlin]

[TABLE="width: 551"]
<colgroup><col span="5"><col><col><col></colgroup><tbody>[TR]
[TD]Col BB[/TD]
[TD]Col BC[/TD]
[TD]Col BD[/TD]
[TD]Col BE[/TD]
[TD]Col BF[/TD]
[TD]Col BG[/TD]
[TD]Reference[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IL[/TD]
[TD]0[/TD]
[TD]iiii[/TD]
[TD]224[/TD]
[TD]773[/TD]
[TD]312[/TD]
[TD]rule #1[/TD]
[TD]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #2[/TD]
[TD]Meets rule when State TX found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]1[/TD]
[TD]iiii[/TD]
[TD]260[/TD]
[TD]574[/TD]
[TD]812[/TD]
[TD]rule #3[/TD]
[TD]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #4[/TD]
[TD]Meets rule when State OH found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]-1[/TD]
[TD]iiii[/TD]
[TD]283[/TD]
[TD]513[/TD]
[TD]937[/TD]
[TD]rule #5[/TD]
[TD]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]MO[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #6[/TD]
[TD]Meets rule when State MO found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #7[/TD]
[TD]Meets rule when State KY found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #8[/TD]
[TD]Meets rule when State MD found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]CT[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #9[/TD]
[TD]Meets rule when State CT found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #10[/TD]
[TD]Meets rule when State MA found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]RI[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #11[/TD]
[TD]Meets rule when State RI found in Column H of 'Data' sheet[/TD]
[/TR]
</tbody>[/TABLE]

 

[wittonlin]

[TABLE="width: 800"]
<tbody>[TR]
[TD]Changes To Sheet 'G2-3'
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]"Target Client Table"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Col BB
[/TD]
[TD]Col BC
[/TD]
[TD]Col BD
[/TD]
[TD]Col BE
[/TD]
[TD]Col BF
[/TD]
[TD]Col BG[/TD]
[TD]Reference
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IL[/TD]
[TD]0[/TD]
[TD]iiii[/TD]
[TD]224[/TD]
[TD]773[/TD]
[TD]312[/TD]
[TD]rule #1 [/TD]
[TD]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #2 [/TD]
[TD]Meets rule when State TX found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]1[/TD]
[TD]iiii[/TD]
[TD]260[/TD]
[TD]574[/TD]
[TD]812[/TD]
[TD]rule #3 [/TD]
[TD]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #4 [/TD]
[TD]Meets rule when State OH found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]-1[/TD]
[TD]iiii[/TD]
[TD]283[/TD]
[TD]513[/TD]
[TD]937[/TD]
[TD]rule #5 [/TD]
[TD]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]MO[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #6 [/TD]
[TD]Meets rule when State MO found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #7 [/TD]
[TD]Meets rule when State KY found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #8 [/TD]
[TD]Meets rule when State MD found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]CT[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #9 [/TD]
[TD]Meets rule when State CT found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1 0[/URL] [/TD]
[TD]Meets rule when State MA found in Column H of 'Data' sheet[/TD]
[/TR]
[TR]
[TD]RI[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rule #1 1[/TD]
[TD]Meets rule when State RI found in Column H of 'Data' sheet[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 192"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 800"]
<tbody>[TR]
[TD="colspan: 3"]Changes To Sheet 'Data'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]"Source Data Tables"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Col H
[/TD]
[TD]Col S
[/TD]
[TD]/ZZ/
[/TD]
[TD]Col BQ[/TD]
[TD]Col BR
[/TD]
[TD]Meets Rule[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Areacode[/TD]
[TD]/ZZ/[/TD]
[TD]Kenny[/TD]
[TD]Ron[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G2-1[/TD]
[TD]G2-3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MI[/TD]
[TD]906[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]409[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD="colspan: 3"]rule #2 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]
[/TR]
[TR]
[TD]MO[/TD]
[TD]903[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD="colspan: 3"]rule #6 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]740[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD="colspan: 3"]rule #4 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]283[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD="colspan: 3"]rule #5 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]937[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD="colspan: 3"]rule #5 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD]517[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD="colspan: 3"]rule [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1 0[/URL] ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]832[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD="colspan: 3"]rule #2 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]574[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD="colspan: 3"]rule #3 ; Col BC = 1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline[/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]614[/TD]
[TD]/ZZ/[/TD]
[TD][/TD]
[TD]y[/TD]
[TD="colspan: 3"]rule #4 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]636[/TD]
[TD]/ZZ/[/TD]
[TD]y[/TD]
[TD][/TD]
[TD="colspan: 3"]rule #8 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]832[/TD]
[TD]/ZZ/[/TD]
[TD]y[/TD]
[TD][/TD]
[TD="colspan: 3"]rule #2 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]812[/TD]
[TD]/ZZ/[/TD]
[TD]y[/TD]
[TD][/TD]
[TD="colspan: 3"]rule #3 ; Col BC = 1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]270[/TD]
[TD]/ZZ/[/TD]
[TD]y[/TD]
[TD][/TD]
[TD="colspan: 3"]rule #7 ; Col BC = 3 = .Pattern = xlGray16, RGB(71,255,163), Default text, Thin Outline[/TD]
[/TR]
[TR]
[TD]CT[/TD]
[TD]860[/TD]
[TD]/ZZ/[/TD]
[TD]y[/TD]
[TD][/TD]
[TD="colspan: 3"]rule #9 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD]413[/TD]
[TD]/ZZ/[/TD]
[TD]y[/TD]
[TD][/TD]
[TD="colspan: 3"]rule [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1 0[/URL] ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]
[/TR]
[TR]
[TD]IL[/TD]
[TD]773[/TD]
[TD]/ZZ/[/TD]
[TD]y[/TD]
[TD][/TD]
[TD="colspan: 3"]rule #1 ; Col BC = 0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline[/TD]
[/TR]
[TR]
[TD]IL[/TD]
[TD]773[/TD]
[TD]/ZZ/[/TD]
[TD]y[/TD]
[TD][/TD]
[TD="colspan: 3"]rule #1 ; Col BC = 0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline[/TD]
[/TR]
</tbody>[/TABLE]

 

[wittonlin]

I know I over-complicated this! Sorry for those whom look at this problem.

Just before I was going to SIMPLIFY I did one more quick search and forgot ALL about COUNTIFS<<!!!

The solution is simply this...

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
i.e.

=COUNTIFS(B2:B7,">0", C2:C7,"=0")

My specific example:

=COUNTIFS('G2-1'!$BB$1:$BB$15,$H101, 'G2-1'!$BC$1:$BC$15,"-1")

Peace!

 

[wittonlin]

HELP!!

I ran into a problem using the "other" formula.

This one works perfectly, either way!

=COUNTIFS('G2-1'!$BB$1:$BB$15,$H101, 'G2-1'!$BC$1:$BC$15,""-1"")

=COUNTIFS('G2-1'!$BC$1:$BC$15,""-1"", 'G2-1'!$BB$1:$BB$15,$H101)

Basically, in addition to considering whether column 'G2-1'!$BC$1:$BC$15 is {-1,0,1,2,3}; it also only adds (conditional formatting) when a state in column H exists in the customer sheet (i.e. 'G2-1'!$BB$1:$BB$15)


The "other" formula works fine on it's own:

=COUNTIF('G2-1'!$BE$1:$BO$15,$S101)

It adds (conditional formatting) when an area code in column S exists in the customer sheet (i.e. 'G2-1'!$BE$1:$BO$15)

BUT NOW ADDING IN THE BC COLUMN, IT DOESN'T WORK!

=COUNTIFS('G2-1'!$BC$1:$BC$15,""-1"", 'G2-1'!$BE$1:$BO$15,$S101)

I have NO idea why. The ONLY difference is the space where the formula looks for, in this case area codes.

In the first formula it only has to look in a portion of ONE column, and the 2nd formula it's looking into a table of 10 or so columns.

 

[Special-K99]

Try

=COUNTIFS('G2-1'!$BC$1:$BC$15,-1, 'G2-1'!$BE$1:$BO$15,$S101)

 

[wittonlin]

Thanks so much for trying Special-K99! It doesn't activate the conditional formatting for some reason though.

Here's a quick overview to see if this helps. I could upload a complete file, but it's only a couple tables in a couple of pages.

The first main sheet is called 'Data'. This omits non-essential columns for space.


[TABLE="class: grid, width: 850, align: left"]
<tbody>[TR]
[TD="align: center"]Col H
[/TD]
[TD="align: center"]Col S[/TD]
[TD="align: center"]Col T[/TD]
[TD="align: center"]Col Z[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]State
[/TD]
[TD="align: center"]Area Code
[/TD]
[TD="align: center"]State Group
[/TD]
[TD="align: center"]G2-2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]408[/TD]
[TD]Group A[/TD]
[TD="align: center"] (3)Lightest Green[/TD]
[TD] < Column with conditional formulas[/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]440[/TD]
[TD]Group A[/TD]
[TD="align: center"] -None-w/ Area Codes, none were listed.[/TD]
[TD] < Of course, "Applied To" in this case is $Z3:$Z17[/TD]
[/TR]
[TR]
[TD]KS[/TD]
[TD]316[/TD]
[TD]Group B[/TD]
[TD="align: center"] -None-[/TD]
[TD]Based on next table...[/TD]
[/TR]
[TR]
[TD]IL[/TD]
[TD]217[/TD]
[TD]Group A[/TD]
[TD="align: center"] (3)Lightest Green[/TD]
[TD](See ex. color coding)[/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]214[/TD]
[TD]Group A[/TD]
[TD="align: center"] (-1)Blacked Out[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NE[/TD]
[TD]402[/TD]
[TD]Group B[/TD]
[TD="align: center"] -None-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]214[/TD]
[TD]Group A[/TD]
[TD="align: center"] (-1)Blacked Out[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MI[/TD]
[TD]269[/TD]
[TD]Group A[/TD]
[TD="align: center"] (1)Med Green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DC[/TD]
[TD]202[/TD]
[TD]Group A[/TD]
[TD="align: center"] -None-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]614[/TD]
[TD]Group A[/TD]
[TD="align: center"] (0)Dark Green-High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]281[/TD]
[TD]Group A[/TD]
[TD="align: center"] (0)Dark Green-High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TN[/TD]
[TD]931[/TD]
[TD]Group B[/TD]
[TD="align: center"]-None-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]765[/TD]
[TD]Group A[/TD]
[TD="align: center"] (2)Med Darker Green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IA[/TD]
[TD]641[/TD]
[TD]Group B[/TD]
[TD="align: center"] -None-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FL[/TD]
[TD]561[/TD]
[TD]Group A[/TD]
[TD="align: center"] (-1)Blacked Out[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



In this case the other sheet is called 'G2-2'

=COUNTIFS('G2-2'!$BC$1:$BC$15,-1, 'G2-2'!$BE$1:$BO$15,$S101)

The trick is when there's no Area Codes it considers only State. It might take two formulas and using the STOP option. Maybe something like...
=COUNTIFS(AND('G2-2'!$BC$1:$BC$15,"-1",'G2-2'!$BD$1:$BD$15="", 'G2-2'!$BE$1:$BO$15,$S101))

And notice two TX entries. One is high priority Area Codes and the other is "-1" so those Area Codes are blacked out.

Again the priority order from highest to lowest is {0,1,2,3,-1) and the conditional formatting is applied appropriately.


[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]Col BB[/TD]
[TD="align: center"]Col BC[/TD]
[TD="align: center"]Col BD[/TD]
[TD="align: center"]Col BE[/TD]
[TD="align: center"]Col BF[/TD]
[TD="align: center"]Col BG[/TD]
[TD="align: center"]To Col BO…[/TD]
[/TR]
[TR]
[TD]Row 1
[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]"list of all[/TD]
[TD="align: center"]408[/TD]
[TD="align: center"]415[/TD]
[TD="align: center"]510[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]area codes..."[/TD]
[TD="align: center"]281[/TD]
[TD="align: center"]713[/TD]
[TD="align: center"]832[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD="align: center"]OH[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]380[/TD]
[TD="align: center"]614[/TD]
[TD="align: center"]513[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD="align: center"]MI[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD="align: center"]IN[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD="align: center"]IL[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]309[/TD]
[TD="align: center"]217[/TD]
[TD="align: center"]618[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Row 7[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]214[/TD]
[TD="align: center"]469[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Row 8
[/TD]
[TD="align: center"]FL[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






Thank you for anyone that can help!
Mark

 

[wittonlin]

It's just SO odd, that this version works perfectly! ...Only highlighting the States where row Column BC=-1

=COUNTIFS('G2-2'!$BB$1:$BB$15,$H101, 'G2-2'!$BC$1:$BC$15,-1)

But the moment I try this where the ONLY real difference is a multiple column range, it doesn't highlight ANY cells!!

=COUNTIFS('G2-2'!$BE$1:$BO$15,$S101, 'G2-2'!$BC$1:$BC$15,-1)

 

[RoryA]

Please note the forum rules on cross-posting, which are the same here as elsewhere, and update your thread accordingly. Thank you.