[TABLE="width: 1000, align: left"]

[TD="colspan: 10"]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.[/TD]

[TD="colspan: 3"]**From Sheet 'G2-3'**[/TD]

[TD="colspan: 3"]** "Target Client Table"**[/TD]

**Col BB**

**Col BC**

**Col BD**

**Col BE**

**Col BF**

**Col BG**

**Reference**

IL

[/td]0

[/td]iiii

[/td]224

[/td]773

[/td]312

[/td]rule #1

[/td]

[TD="colspan: 3"]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]

TX

[/td]2

[/td]rule #2

[/td]

[TD="colspan: 3"]Meets rule when State TX found in Column H of 'Data' sheet[/TD]

IN

[/td]1

[/td]iiii

[/td]260

[/td]574

[/td]812

[/td]rule #3

[/td]

[TD="colspan: 3"]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]

OH

[/td]2

[/td]rule #4

[/td]

[TD="colspan: 3"]Meets rule when State OH found in Column H of 'Data' sheet[/TD]

OH

[/td]-1

[/td]iiii

[/td]283

[/td]513

[/td]937

[/td]rule #5

[/td]

[TD="colspan: 3"]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]

MO

[/td]2

[/td]rule #6

[/td]

[TD="colspan: 3"]Meets rule when State MO found in Column H of 'Data' sheet[/TD]

KY

[/td]3

[/td]rule #7

[/td]

[TD="colspan: 3"]Meets rule when State KY found in Column H of 'Data' sheet[/TD]

MD

[/td]-1

[/td]rule #8

[/td]

[TD="colspan: 3"]Meets rule when State MD found in Column H of 'Data' sheet[/TD]

CT

[/td]-1

[/td]rule #9

[/td]

[TD="colspan: 3"]Meets rule when State CT found in Column H of 'Data' sheet[/TD]

MA

[/td]-1

[/td]rule #10

[/td]

[TD="colspan: 3"]Meets rule when State MA found in Column H of 'Data' sheet[/TD]

RI

[/td]-1

[/td]rule #11

[/td]

[TD="colspan: 3"]Meets rule when State RI found in Column H of 'Data' sheet[/TD]

[/TABLE]

[TABLE="width: 1000"]

[TD="width: 192, colspan: 3"]**From Sheet 'Data'**

[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"][/TD]

[TD="width: 92"][/TD]

[TD="width: 75"][/TD]

[TD="colspan: 3"]** "Source Data Tables"**[/TD]

[TD="width: 64"]__ Col H__[/TD]

[TD="width: 64"]

__[/TD]__

**Col S**

[TD="width: 64"]

__[/TD]__

**/ZZ/**

[TD="width: 64"]

__[/TD]__

**Col BQ**

[TD="width: 64"]

__[/TD]__

**Col BR**

[TD="width: 92"]

__[/TD]__

**Meets Rule**

[TD="width: 64"]**State**[/TD]

[TD="width: 64"]**Areacode**[/TD]

[TD="width: 64"]**/ZZ/**[/TD]

[TD="width: 64"]

**Kenny**

[/TD]

[TD="width: 64"]

**Ron**[/TD]

[TD="width: 92"][/TD]

[TD="width: 64"]**G2-1**[/TD]

[TD="width: 64"]**G2-3**[/TD]

[TD="width: 64"]MI[/TD]

[TD="width: 64"]906[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

N/A

[/td]

[TD="width: 64"]TX[/TD]

[TD="width: 64"]409[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

[TD="colspan: 2"]rule #2 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]MO[/TD]

[TD="width: 64"]903[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

[TD="colspan: 2"]rule #6 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]OH[/TD]

[TD="width: 64"]740[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

[TD="colspan: 2"]rule #4 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]OH[/TD]

[TD="width: 64"]283[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

[TD="colspan: 2"]rule #5 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]OH[/TD]

[TD="width: 64"]937[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

[TD="colspan: 2"]rule #5 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]MA[/TD]

[TD="width: 64"]517[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

[TD="colspan: 2"]rule #10 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]TX[/TD]

[TD="width: 64"]832[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

[TD="colspan: 2"]rule #2 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]IN[/TD]

[TD="width: 64"]574[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

[TD="colspan: 2"]rule #3 ; Col BC = 1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline[/TD]

[TD="width: 64"]OH[/TD]

[TD="width: 64"]614[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"][/TD]

[TD="width: 64"]y[/TD]

[TD="colspan: 2"]rule #4 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]MD[/TD]

[TD="width: 64"]636[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"]y[/TD]

[TD="width: 64"][/TD]

[TD="colspan: 2"]rule #8 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]TX[/TD]

[TD="width: 64"]832[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"]y[/TD]

[TD="width: 64"][/TD]

[TD="colspan: 2"]rule #2 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]IN[/TD]

[TD="width: 64"]812[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"]y[/TD]

[TD="width: 64"][/TD]

[TD="colspan: 2"]rule #3 ; Col BC = 1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline[/TD]

[TD="width: 64"]KY[/TD]

[TD="width: 64"]270[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"]y[/TD]

[TD="width: 64"][/TD]

[TD="colspan: 2"]rule #7 ; Col BC = 3 = .Pattern = xlGray16, RGB(71,255,163), Default text, Thin Outline[/TD]

[TD="width: 64"]CT[/TD]

[TD="width: 64"]860[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"]y[/TD]

[TD="width: 64"][/TD]

[TD="colspan: 2"]rule #9 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]MA[/TD]

[TD="width: 64"]413[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"]y[/TD]

[TD="width: 64"][/TD]

[TD="colspan: 2"]rule #10 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]IL[/TD]

[TD="width: 64"]773[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"]y[/TD]

[TD="width: 64"][/TD]

[TD="colspan: 2"]rule #1 ; Col BC = 0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline[/TD]

[TD="width: 64"]IL[/TD]

[TD="width: 64"]773[/TD]

[TD="width: 64"]/ZZ/[/TD]

[TD="width: 64"]y[/TD]

[TD="width: 64"][/TD]

[TD="colspan: 2"]rule #1 ; Col BC = 0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline[/TD]

[/TABLE]

[TABLE="width: 851"]

[TD="colspan: 3"]**COLUMN BC RANKING**[/TD]

[TD="colspan: 8"]Col BC = 0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline[/TD]

[TD="colspan: 8"]Col BC = 1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline[/TD]

[TD="colspan: 8"]Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="colspan: 8"]Col BC = 3 = .Pattern = xlGray16, RGB(71,255,163), Default text, Thin Outline[/TD]

[TD="colspan: 8"]Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[/TABLE]

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.

.

**Any ideas how to manipulate the main formulas to include reference to Column Values = {0,1,2,3,-1} in Column 'BC3:BC15' on the Client Sheet? In this case Sheet 'G2-3'**

**Also seen on this Forum post.**