If two cells in two cells have the same conditional formatting(Colour) then output text "Match" in a third cell

[ExcelHellsBells]

Hello,

Simple goal but not so simple answer according to Mr google...

I have 2 columns of data col#1 (B3:B105) Col#2 (D3:D105) which have conditional formating fill colour applied to them based on the number value in the cell. I want to implement a third column (F3:F105) to result in a "Match" or "True or False" output after comparing each cell row by row, e.g: B3(CF-Red)<--->D3(CF-Black) = "False", B4(CF-Black)<--->D4(CF-Black) = "True" or "Match".

There seems to be some methods in the link below that either work or don't work depending on excel version over the years and some VBA stuff (which I don't have a clue about but willing to give a go)

VBA Code to find Interior Color set by Conditional Formatting

Hi All: I have a spreadsheet where I have set up Conditional Formatting in some cells. IF certain criteria are not met then the cell turns red .ColorIndex = 3 Is there any VBA Code that can detect interior color that was set up by Conditional Formatting. I have been seraching the internet...

www.mrexcel.com

Ideally, I want this to happen in real-time so it populates as the numbers in column D are populated so they are compared row by row with the already populated column B.



would love some guidance, thank you!

 

[Fluff]

Hi & welcome to MrExcel.
You can use a formula with the same rules as the CF to determine if B & D match.

 

[ExcelHellsBells]

Hi & welcome to MrExcel.
You can use a formula with the same rules as the CF to determine if B & D match.

Wow, and here I was thinking that was not possible. I thought that you could not use the inbuilt functions in excel to match conditional formatting states of 2 cells purely based on their CF colour.

 

[ExcelHellsBells]

actually thinking about it. I have a load of CF happening as it is based on each individual number not a range of numbers. It is based on the colours/numbers in roulette (1=red, 2 = black etc...)

would I need to repeat the formula for each individual instance then?

 

[Fluff]

For something like that I would recommend creating a lookup table similar to cols H & I and then you can use

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3		16		13		FALSE		0	G
4		1		15		TRUE		1	R
5		12		6		TRUE		2	B
6		12						3	R
7		2						4	B
8		27						5	R
9		11						6	B
10		28						7	R
11		16						8	B
12		25						9	R
13		28						10	B
14		23						11	R
15		27						12	B
16		1						13	R
17		35						14	B
18		22						15	R
19		10						16	B
20		19						17	R
21		29						18	B
Master

Cell Formulas
Range	Formula
F3:F6	F3	=IF(D3="","",FILTER($I$3:$I$39,$H$3:$H$39=B3)=FILTER($I$3:$I$39,$H$3:$H$39=D3))

 

[ExcelHellsBells]

Thank you. That is exactly what I was looking at here:

Matching number and color in roulette simulator

I am trying to build an roulette game in Excel. Due to this blog post I am able to randomly select numbers that are provided in column D Spin number. Now I would like to auto-fill E, Spin color bas...

stackoverflow.com

will give it a go and let you know

 

[Fluff]

Ok

 

[ExcelHellsBells]

For something like that I would recommend creating a lookup table similar to cols H & I and then you can use

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3		16		13		FALSE		0	G
4		1		15		TRUE		1	R
5		12		6		TRUE		2	B
6		12						3	R
7		2						4	B
8		27						5	R
9		11						6	B
10		28						7	R
11		16						8	B
12		25						9	R
13		28						10	B
14		23						11	R
15		27						12	B
16		1						13	R
17		35						14	B
18		22						15	R
19		10						16	B
20		19						17	R
21		29						18	B
Master

Cell Formulas
Range	Formula
F3:F6	F3	=IF(D3="","",FILTER($I$3:$I$39,$H$3:$H$39=B3)=FILTER($I$3:$I$39,$H$3:$H$39=D3))

Thank you, this worked. For some reason my column F had turned into a text field format which messed me about a bit then I tried in another spreadsheet to verify and it worked as a General cell

thank you so much!
ok I am getting a "'VALUE!" error message show up

B	D	F	J	K

 

[Fluff]

Do you have a #VALUE! anywhere in your lookup table?

 

[ExcelHellsBells]

Do you have a #VALUE! anywhere in your lookup table?

sorry, it works fine, that (the 'VALUE!) was something I had in the post before I went back to have another go at the spreadsheet but didn't realise it was still in my comment. I don't think I can go back and edit the comment