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

 

[Oaktree]

Since you already have a UDF for GetFillColor, you should be able to use:

W6=(GetFillColor(L6) = GetFillColor(Q6)) copied down and right.

 

[sksanjeev786]

Since you already have a UDF for GetFillColor, you should be able to use:

W6=(GetFillColor(L6) = GetFillColor(Q6)) copied down and right.

Hi Oaktree,

Thanks for your time on this i have tried as per the above Function but not able to get the exact data for color pleae findhte Screen shot

 

[Oaktree]

*Are* they actually the same? If you put =GetFillColor(M6) in one cell and =GetFillColor(R6) in another cell, what does each return?

 

[sksanjeev786]

*Are* they actually the same? If you put =GetFillColor(M6) in one cell and =GetFillColor(R6) in another cell, what does each return?

Hi Oaktree,

yes the font color same and both RGB are matching
just wanted to let you know column M color i have applied Conditional formatting and for column R color was copied from PPT file

FS.xlsm
	K	L	M	N	O	P	Q	R
6	Unaided Brand Awareness - First Mention	1%	+0.2				1	0.2
7	Unaided Brand Awareness - Any Mention	19%	+0.2				19	0.2
8	Unaided Brand Awareness - Other Mentions	18%	+0.1				18	0.1
9	Aided Awareness	96%	-0.9				96	-0.9
10	Total Ad Awareness	33%	+13.4				33	13.4
11	Online Ad Awareness	20%	+8.1				20	8.1
12	CTV Ad Awareness	8%	+3.4				8	3.4
13	Message Association - "We Got Now"	9%	+1.1				9	1.1
14	Brand Favorability	57%	+1.4				57	1.4
15	Consideration Intent	49%	+6.2				49	6.2
16	Recommendation - T3B	33%	+8.4				33	8.4
17	Recommendation - B3B	11%	+2.1				11	2.1
18							324/648
19
20	n	#REF!	#REF!				324/648
21		#REF!
22
23
24
25
26
27
28
29
30		B	D
31		2	4
32
33	9	2	9
34	10	2	15
35	11	2	15
36	12	2	9
37	13	2	43
38	14	2	45
39	15	2	45
40	18	2	15
41	19	2	15
42	20	2	43
43	21	2	43
44	22	2	15
Data

Cell Formulas
Range	Formula
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
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
L21	L21	=L20&"/"&M20
L30:M30	L30	=CHAR(L31+64)
L31:M31	L31	=L2
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
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

 

[Oaktree]

I’m guessing your GetFillColor UDF isn’t designed to return fill colors applied by conditional formatting and that the test from my previous post would return different values.

See Conditional Formatting Colors for a different UDF you could use to return the conditional format fill color for column L, which you could then compare against GetFillColor for column R.

 

[sksanjeev786]

I’m guessing your GetFillColor UDF isn’t designed to return fill colors applied by conditional formatting and that the test from my previous post would return different values.

See Conditional Formatting Colors for a different UDF you could use to return the conditional format fill color for column L, which you could then compare against GetFillColor for column R.

Okay! Thanks for your time on thsi.Can you please help me out with this if possible

 

[sksanjeev786]

Okay! Thanks for your time on thsi.Can you please help me out with this if possible

Hi,

I have checked with all the VBA and didn't get cell color where i have a color with Cf. and getting same score.

FS.xlsm
	M	N	O	P
5		=ActiveCondition(M6)	=GetStrippedValue(M6)	=ColorOfCF(M6,FALSE)
6	+0.2	3	0.241851222466873	2818735
7	+0.2	3	0.174793445415908	2818735
8	+0.1	3	8.72632106033555E-02	2818735
9	-0.9	3	-0.871100064041497	2818735
10	+13.4	3	13.3670430511547	2818735
11	+8.1	3	8.10713860524381	2818735
12	+3.4	3	3.44730159244791	2818735
13	+1.1	3	1.13967321395782	2818735
14	+1.4	3	1.3667861543084	2818735
15	+6.2	3	6.17006611501388	2818735
16	+8.4	3	8.36438867506673	2818735
17	+2.1	3	2.1439191205221	2818735
18		3		2818735
19		3		2818735
Data

Cell Formulas
Range	Formula
N5:P5	N5	=FORMULATEXT(N6)
N6:N19	N6	=ActiveCondition(M6)
O6:O19	O6	=GetStrippedValue(M6)
P6:P19	P6	=ColorOfCF(M6,FALSE)
M6:M17	M6	=INDEX(INDIRECT("'"&$G6&$H6&"'!A"&$I6&":IV"&$J6&""),MATCH($K6,INDIRECT("'"&$G6&$H6&"'!A"&$I6&":A"&$J6&""),0),M$2)*100

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O5	Cell Value	contains "Delta"	text	NO
L4:N5,Y4:AD5,P4:T5	Cell Value	contains "Delta"	text	NO
M6:M19	Expression	=M33=$AA$1	text	NO
M6:M19	Expression	=M33=$AA$2	text	NO
M6:M19	Expression	=M33=$AA$3	text	NO

 

[Leo Skywalker]

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.

 

[Oaktree]

Leo:

You can't obtain the fill color from CF cells with a UDF

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:

NOTE: ActiveCondition may result in an inaccurate result if the following are true:

• You are calling ActiveCondtion from a worksheet cell, AND
• The cell passed to ActiveCondtion uses a "Formula Is" rather than
  "Cell Value Is" condition, AND
• The formula used in the condition formula contains relative addresses

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