Need to check color true and false

[sksanjeev786]

Hi Team
I have data L6 to M17 (pulled from Excel) and Q6 to R17 (pulled from a PPT file) and i want to cross-check the color by true and false
if the color not match i need false in (W6 to X17)

FS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Data						n	7				Site All Hits: Hulu	0										43	4	p
2	Weight Details											2	4										45	50	r
3	Weighting Scheme	Mid Campaign SLW 6/1/22-9/30/22						8															9	3	q
4							slide 18					Control	Delta										Excel	PPT
5
6								Data	1	55	Unaided Brand Awareness - First Mention	1%	+0.2				1	0.2					TRUE	TRUE
7		Site All Hits: Hulu						Data	1	55	Unaided Brand Awareness - Any Mention	19%	+0.2				19	0.2					TRUE	TRUE
8	Brand Metrics	Control	Exposed	Delta				Data	1	55	Unaided Brand Awareness - Other Mentions	18%	+0.1				18	0.1					TRUE	TRUE
9	Unaided Brand Awareness - First Mention	1.3%	1.5%	0.2%				Data	1	55	Aided Awareness	96%	-0.9				96	-0.9					TRUE	TRUE
10	Unaided Brand Awareness - Any Mention	18.8%	19.0%	0.2%				Data	1	55	Total Ad Awareness	33%	+13.4				33	13.4					TRUE	TRUE
11	Unaided Brand Awareness - Other Mentions	17.5%	17.6%	0.1%				Data	1	55	Online Ad Awareness	20%	+8.1				20	8.1					TRUE	TRUE
12	Aided Awareness	96.2%	95.4%	-0.9%				Data	1	55	CTV Ad Awareness	8%	+3.4				8	3.4					TRUE	TRUE
13	Total Ad Awareness	32.6%	46.0%	13.4%				Data	1	55	Message Association - "We Got Now"	9%	+1.1				9	1.1					TRUE	TRUE
14	Online Ad Awareness	20.0%	28.1%	8.1%				Data	1	55	Brand Favorability	57%	+1.4				57	1.4					TRUE	TRUE
15	CTV Ad Awareness	8.3%	11.7%	3.4%				Data	1	55	Consideration Intent	49%	+6.2				49	6.2					TRUE	TRUE
16	TV Ad Awareness	8.5%	11.7%	3.2%				Data	1	55	Recommendation - T3B	33%	+8.4				33	8.4					TRUE	TRUE
17	Social Ad Awareness	16.8%	27.3%	10.5%				Data	1	55	Recommendation - B3B	11%	+2.1				11	2.1					TRUE	TRUE
18	Message Association - "We Got Now"	8.9%	10.0%	1.1%													324/648
19	Brand Favorability	56.7%	58.0%	1.4%
20	Consideration Intent	48.9%	55.1%	6.2%			[Mediahub New Balance_Banner Books Data for DD Charting.xlsx]	Banner Book 14	1	100	n	324	648				324/648						TRUE	TRUE
21	Recommendation - T3B	32.7%	41.0%	8.4%								324/648
22	Recommendation - B3B	11.0%	13.1%	2.1%
23	Net Promoter: Promoter	20.9%	28.7%	7.8%
24	Net Promoter Score	-34.3%	-19.0%	15.3%
25	Net Promoter: Passive	23.9%	23.6%	-0.3%
26	Net Promoter: Detractors	55.2%	47.7%	-7.5%
27	n	324	648	648
28
29
30												B	D
31												2	4
32
33										Unaided Brand Awareness - First Mention	9	2	9
34										Unaided Brand Awareness - Any Mention	10	2	15
35										Unaided Brand Awareness - Other Mentions	11	2	15
36										Aided Awareness	12	2	9
37										Total Ad Awareness	13	2	43
38										Online Ad Awareness	14	2	45
39										CTV Ad Awareness	15	2	45
40										Message Association - "We Got Now"	18	2	15
41										Brand Favorability	19	2	15
42										Consideration Intent	20	2	43
43										Recommendation - T3B	21	2	43
44										Recommendation - B3B	22	2	15
45
Data

Cell Formulas
Range	Formula
H1	H1	=H3-1
L1:M1	L1	=INDEX(INDIRECT("'"&$G$6&$H$6&"'!A1:IV100"),$H$1,L$2)
W1:X3	W1	=Getfillcolor(W1)
M2	M2	=L2+2
H3	H3	=MATCH("Control",INDIRECT("'"&$G$6&$H$6&"'!B:B"),0)
L4:M4	L4	=INDEX(INDIRECT("'"&$G$6&$H$6&"'!A1:IV100"),$H$3,L$2)
L6:L17,L20	L6	=INDEX(INDIRECT("'"&$G6&$H6&"'!A"&$I6&":IV"&$J6&""),MATCH($K6,INDIRECT("'"&$G6&$H6&"'!A"&$I6&":A"&$J6&""),0),L$2)
M6:M17	M6	=INDEX(INDIRECT("'"&$G6&$H6&"'!A"&$I6&":IV"&$J6&""),MATCH($K6,INDIRECT("'"&$G6&$H6&"'!A"&$I6&":A"&$J6&""),0),M$2)*100
W6:W17	W6	=ROUND(L6*100,0)=Q6
X6:X17	X6	=ROUND(M6,1)=R6
G20	G20	=VLOOKUP($G$4,Sheet3!$A$2:$C$25,2,0)
H20	H20	=VLOOKUP($G$4,Sheet3!$A$2:$C$25,3,0)
M20	M20	=INDEX(INDIRECT("'"&$G20&$H20&"'!A"&$I20&":IV"&$J20&""),MATCH($K20,INDIRECT("'"&$G20&$H20&"'!A"&$I20&":A"&$J20&""),0),M$2-1)
Q20	Q20	=Q18
W20:X20	W20	=L21=Q20
L21	L21	=L20&"/"&M20
L30:M30	L30	=CHAR(L31+64)
L31:M31	L31	=L2
J33:J44	J33	=K6
K33:K44	K33	=MATCH($K6,INDIRECT("'"&$G6&$H6&"'!A"&$I6&":A"&$J6&""),0)
L33:M44	L33	=Getfillcolor(INDIRECT("'"&$G6&$H6&"'!"&L$30&$K33&""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
W6:Z20	Cell Value	contains "f"	text	NO
L4:N5,P4:P5,U4:Z5	Cell Value	contains "Delta"	text	NO
K33:K67	Cell Value	duplicates	text	NO
M6:M19	Expression	=M33=$W$1	text	NO
M6:M19	Expression	=M33=$W$2	text	NO
M6:M19	Expression	=M33=$W$3	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G4	List	=Sheet3!$D$2:$D$99

 

[sksanjeev786]

Hi there,

You can't obtain the fill color from CF cells with a UDF.
You can however, color the cells with the same color given by the CF with a Sub, that way, your UDF will work.

Select the range with the CF colors and run the macro.

Here is the code:

Sub FillCellWithCFColor()
Dim myRange As Range
Set myRange = Selection

Dim cell As Range
For Each cell In myRange
    cell.Interior.Color = cell.DisplayFormat.Interior.Color
Next cell

End Sub

You can delete CF after that, the colors will remain, this time, not conditional.

Hi,

Thanks for your help on this
i have applied this in my database but as I have to pull the data from multiple sheets so CF color will varies
and with this, i can only check once only.

if this is not possible then i have to use the above macro

 

[sksanjeev786]

Leo:

this actually *is* possible! See the method on Chip Pearson's webpage per the link in my previous post

One (maybe two) things will fix this for you:

1) Per the note at the top of Chip's page:

As such, instead of having the conditional formatting rule be "Use a formula to determine which cells to format", use "Format only cells that contain". You can still link the values to AA1, AA2, and AA3 respectively, but this will circumvent the issue Chip explained, as I think your use case hits on all three of those.

2) Note that, if your GetFillColor returns the interior.colorindex (and not the interior.color), you'd use ColorIndexofCF instead of ColorofCF

Hi Oaktree,

Thanks for your time and hard work on this.

With the above Macro, i can use cell one-time cell color with PPT file as mentioned above still can is it possible to get the interior color based on the CF

I hope you understand the scenario i am looking for