Im lost can you help?? - Conditional Format & search

[Krucifire]

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K	L
5			AIRCON	CARPETS	DOORS	ELECTRICAL	HWS	HW24	KITCHEN	PLUMBING	RWT	SHEDS
6	Belconnen	ACT
7	Fyshwick	ACT
8	Tuggeranong	ACT
9	Albury	NSW
10	Artarmon	NSW
11	Ashfield	NSW
12	Auburn	NSW
13	Ballina	NSW
14	BankstownAirport	NSW
15	BatemansBay	NSW
16	Bathurst	NSW
17	Belmont	NSW
18	Belrose	NSW
19	Blacktown	NSW
20	Bonnyrigg	NSW
21	Campbelltown	NSW
22	Cardiff	NSW
23	Caringbah	NSW
24	Carlingford	NSW
25	Cessnock	NSW
26	Chatswood	NSW
27	CoffsHarbourSouth	NSW
28	CoffsHarbourTrade	NSW
29	Crossroads	NSW
30	Dural	NSW
31	ErinaNorth	NSW
32	Forster	NSW
33	Glendale	NSW
34	Gordon	NSW
35	Gosford	NSW
36	Grafton	NSW
37	Griffith	NSW
38	KempseyNorth	NSW
39	KempseySouth	NSW
40	Kotara	NSW
41	LakeHaven	NSW
42	Lidcombe	NSW
43	Lismore	NSW
44	Maitland	NSW
45	Mascot	NSW
46	McGrathsHill	NSW
47	Minchinbury	NSW
48	MossVale	NSW
49	Mudgee	NSW
50	Narrabeen	NSW
51	NelsonBay	NSW
52	NorthParramatta	NSW
53	NorthPenrith	NSW
54	Norwest	NSW
55	Nowra	NSW
56	Orange	NSW
57	Penrith	NSW
58	PortMacquarie	NSW
59	Rockdale	NSW
60	RoseBay	NSW
61	SevenHills	NSW
62	ShellHarbour	NSW
63	Tamworth	NSW
64	Taree	NSW
65	Thornleigh	NSW
66	Tuggerah	NSW
67	TweedHeads	NSW
68	Ulladulla	NSW
69	Unanderra	NSW
70	ValleyHeights	NSW
71	Villawood	NSW
72	WaggaWagga	NSW
73	Warrawong	NSW
74	WarringahMall	NSW
75	Wollongong	NSW
76	Darwin	NT
77	Palmerston	NT
78	Albion	QLD
79	Booval	QLD
80	BrownsPlains	QLD
81	Bundaberg	QLD
82	BurleighHeads	QLD
83	Cairns	QLD
84	Caloundra	QLD
85	CannonHill	QLD
86	Capalaba	QLD
87	Carseldine	QLD
88	ComptonRd,Underwood	QLD
89	Gladstone	QLD
90	Harbourtown	QLD
91	HerveyBay	QLD
92	Lawnton	QLD
93	Mackay	QLD
94	Maroochydore	QLD
95	Morayfield	QLD
96	MtGravatt	QLD
97	MtIsa	QLD
98	Nerang	QLD
99	Noosaville	QLD
100	Oxley	QLD
101	Rockhampton	QLD
102	Rocklea	QLD
103	Rothwell	QLD
104	Southport	QLD
105	Stafford	QLD
106	ToowoombaWest	QLD
107	Townsville	QLD
108	Underwood	QLD
109	VictoriaPoint	QLD
110	AllenbyGardens	SA
111	KentTown	SA
112	Marion	SA
113	MileEnd	SA
114	Modbury	SA
115	MtGambier	SA
116	MunnoPara	SA
117	Noarlunga	SA
118	Parafield	SA
119	WindsorGardens	SA
120	Launceston	TAS
121	Moonah	TAS
122	RosnyPark	TAS
123	Altona	VIC
124	Ballarat	VIC
125	Bayswater	VIC
126	Bendigo	VIC
127	BoxHill	VIC
128	Broadmeadows	VIC
129	Coburg	VIC
130	Corio	VIC
131	Cranbourne	VIC
132	Croydon	VIC
133	Dandenong	VIC
134	Eltham	VIC
135	Epping	VIC
136	FountainGate	VIC
137	Frankston	VIC
138	HoppersCrossing	VIC
139	Keysborough	VIC
140	Maribyrnong	VIC
141	Melton	VIC
142	Mentone	VIC
143	Mildura	VIC
144	MillPark	VIC
145	Moorabbin	VIC
146	Mornington	VIC
147	Morwell	VIC
148	Northland	VIC
149	NottingHill	VIC
150	Nunawading	VIC
151	PortMelbourne	VIC
152	Rosebud	VIC
153	Sandown	VIC
154	Scoresby	VIC
155	Shepparton	VIC
156	SouthOakleigh	VIC
157	Sunbury	VIC
158	Sunshine	VIC
159	TaylorsLakes	VIC
160	Thomastown	VIC
161	VermontSouth	VIC
162	Warrnambool	VIC
163	WaurnPonds	VIC
164	Wodonga	VIC
165	WodongaNorth	VIC
166	Albany	WA
167	Balcatta	WA
168	Belmont	WA
169	BibraLake	WA
170	Bunbury	WA
171	Busselton	WA
172	Cannington	WA
173	Claremont	WA
174	Esperance	WA
175	Geraldton	WA
176	Homebase	WA
177	Inglewood	WA
178	Innaloo	WA
179	Joondalup	WA
180	Kalamunda	WA
181	Kalgoorlie	WA
182	Maddington	WA
183	Malaga	WA
184	Mandurah	WA
185	Melville	WA
186	Midland	WA
187	Mindarie	WA
188	Morley	WA
189	O'Connor	WA
190	PortKennedy	WA
191	Rockingham	WA
192	SouthPerth	WA
193	Whitfords	WA
194	Willetton	WA
Sheet1

above is what i need to populate base on the sample data (in second post)


is what i need it do to is search the Data in Sheet2! for the Category (row 5) then by the store (column A) if there is some1 in the data in sheet2! that meets these 2 criteria i want it to put the letter Y in the corresponding cell. so that i can do a conditional format on the cell GREEN if it contains a Y Red if cell is empty.

The conditional format part is easy.

the doing the check part is what i am not sure on how to do.

Await second post before reply thanks


- Troy

 

[Krucifire]

SRT_WIP.xls
	A	B	C	D	E
1	ID	ContactType	Company	ProductCategories	CoverageRegion-NSW
2	1	ActiveTradeOperator	1StyleFloors	Carpets
3	2	ActiveTradeOperator	5Ft4TimberFlooring	TimberFlooring
4	3	ActiveTradeOperator	A&KFencing	PoolFencing;#Sheds;#GeneralHandyman(ClothesLine)	Gosford;#Tuggerah
5	4	ActiveTradeOperator	A&DKitchens	Kitchens;#Doors
6	5	ActiveTradeOperator	AableCarpetServices	Carpets	Artarmon;#Belrose;#Chatswood;#Gordon;#Thornleigh;#WarringahMall
7	6	ActiveTradeOperator	AbelRefrigeration	Airconditioning;#Electrical	Shellharbour;#Warrawong;#Wollongong
8	7	ActiveTradeOperator	AcademyPlumbing	Plumbing;#RWT	Belmont;#Cardiff;#Cessnock;#Glendale;#Gosford;#Kotara;#Maitland;#Tuggerah
9	8	ActiveTradeOperator	AcademyPlumbing
10	9	ActiveTradeOperator	AdelaideApplianceInstallers	Airconditioning;#24hrHWS(Gas&Electric);#HWS-All;#HWS-Solar;#Electrical
11	10	ActiveTradeOperator	AdvancedElectrical&DataSolutionsPtyLtd	Airconditioning;#Electrical
ALL

SAMPLE DATA

 

[Krucifire]

To help explain is what i am trying to achieve.

FYI. This code fails miserably.

 Select Case Sheet1!C6 = ""
        Sheets("Sheet2").Activate
                If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
                Selection.AutoFilter Field:=2, Criteria1:="Active Trade Operator"
                Selection.AutoFilter Field:=13, Criteria1:="ACT"
                Selection.AutoFilter Field:=17, Criteria1:="*=Bel*", Operator:=xlAnd
                Selection.AutoFilter Field:=16, Criteria1:="*=Air*", Operator:=xlAnd
                If Sheet2!A2.Cells.Value = True Then
                Sheet1!C6 = "Y"
                Else
                Sheet1!C6 = "N"
                End If
  Select Case Sheet1!C7 = ""
             Sheets("Sheet2").Select
               If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
                 Selection.AutoFilter Field:=2, Criteria1:="Active Trade Operator"
                Selection.AutoFilter Field:=13, Criteria1:="ACT"
                Selection.AutoFilter Field:=17, Criteria1:="*=Fys*", Operator:=xlAnd
                Selection.AutoFilter Field:=16, Criteria1:="*=Air*", Operator:=xlAnd
                If Sheet2!A2.Cells.Value = True Then
                Sheet1!C7 = "Y"
                Else
                Sheet1!C7 = "N"
                End If

 

[Krucifire]

Hey all,

just checking in to see if there is any additional information that you require to help provide a solution to this problem??

-Troy