How to conditionally format a table to highlight cells based on a list of coordinates that you enter?

[jennuine]

I wracked my brain over this one and I'm honestly not sure how to proceed but I'm willing to learn and work this through. If you can point me in a direction I would be immensely grateful.

Problem: I have a seating plan where the row and column together give me the position of a participant's table. As I enter the data on the right table ("Participants' Tables"), the left table ("Seating Map") should highlight the cell that has the same value. For example, if I enter "A58" under column Table on the right, the cell that is in column "A" with the value 58 should be highlighted orange (cell A9). Is there a way I can ask Excel to do this for me automatically instead of my having to manually search and highlight the position of the table? Thank you for your help in advance.

SEATING PLAN.xlsx
	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	Z	AA	AB	AC	AD	AE	AF
1	SEATING MAP																			PARTICIPANTS' TABLES
2
3	A		B	C		D	E		F	G		H	I		J	K		L		PARTICIPANTS										TABLE
4				62			62			62			62			62				Person 1										A	58
5			61	61			61			61			61			61				Person 2										G	45
6			60	60		60	60		60	60		60	60		60	60		60		Person 3										K	56
7	59		59	59		59	59		59	59		59	59		59	59		59
8	58		58	58		58	58		58	58		58	58		58	58		58
9	57		57	57		57	57		57	57		57	57		57	57		57
10	56			56			56			56			56			56
11	55		55	55		55	55		55	55		55	55		55	55		55
12	54		54	54		54	54		54	54		54	54		54	54		54
13	53		53	53		53	53		53	53		53	53		53	53		53
14	52		52	52		52	52		52	52		52	52		52	52		52
15				51			51			51			51			51
16	50		50	50		50	50		50	50		50	50		50	50		50
17	49		49	49		49	49		49	49		49	49		49	49		49
18	48		48	48		48	48		48	48		48	48		48	48		48
19	47		47	47		47	47		47	47		47	47		47	47		47
20	46			46			46			46			46			46
21	45		46	45		45	45		45	45		45	45		45	45		45
22	44		44	44		44	44		44	44		44	44		44	44		44
23	43		43	43		43	43		43	43		43	43		43	43		43
24			42	42		42			42	42		42	42		42	42		42
25
26	41			41			41			41			41			41
27	40			40			40			40			40			40
28	39		39	39		39	39		39	39		39	39		39	39		39
29	38		38	38		38	38		38	38		38	38		38	38		38
30	37		37	37		37	37		37	37		37	37		37	37		37
31	36		36	36		36	36		36	36		36	36		36	36		36
32	35			35			35			35			35			35
33	34		34	34		34	34		34	34		34	34		34	34		34
34	33		33	33		33	33		33	33		33	33		33	33		33
35	32		32	32		32	32		32	32		32	32		32	32		32
36	31		31	31		31	31		31	31		31	31		31	31		31
37	30			30			30			30			30			30
38	29		29	29		29	29		29	29		29	29		29	29		29
39	28		28	28		28	28		28	28		28	28		28	28		28
40	27		27	27		27	27		27	27		27	27		27	27		27
41	26		26	26		26	26		26	26		26	26		26	26		26
42
43	24
44	23
45	22
46	21			21			21			21			21			21
47	20			20			20			20			20			20
48	19		19	19		19	19		19	19		19	19		19	19		19
49	18		18	18		18	18		18	18		18	18		18	18		18
50	17		17	17		17	17		17	17		17	17		17	17		17
51	16		16	16		16	16		16	16		16	16		16	16		16
52				15			15			15			15			15
53	14		14	14		14	14		14	14		14	14		14	14		14
54	13		13	13		13	13		13	13		13	13		13	13		13
55	12		12	12		12	12		12	12		12	12		12	12		12
56	11		11	11		11	11		11	11		11	11		11	11		11
57	10			10			10			10			10			10
58	09		09	09		09	09		09	09		09	09		09	09		09
59	08		08	08		08	08		08	08		08	08		08	08		08
60	07		07	07		07	07		07	07		07	07
61	06		06	06					06	06		06	06
62			05	05						05			05
63	04		04	04		04	04		04	04		04	04
64	03		03	03		03	03		03	03		03	03
65	02		02	02		02	02		02	02		02	02
66	01		01	01		01	01		01	01		01	01
TEMP

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:R66	Expression	=IF	text	NO

 

[RudRud]

Kindly give a shot @jennuine

Right click sheet1 -> view code -> paste below code then run the code by pressing F5

the code will let you choose either yes or no
Yes: start formatting, If not found will give you notification and put red background color in AE exact Row
No: The code will only Clear/Reset background color

Unsure if using formula, this one probably can be your reference

Option Explicit
Sub test()
Dim ws As Worksheet
Set ws = Sheets("sheet1")
Dim c As Range
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Comparemode = vbTextCompare
Dim answer As Integer
Dim i As Integer
Dim a As Variant
Dim ss As Range

answer = MsgBox("Do you want update color", vbQuestion + vbYesNo + vbDefaultButton2, "Format Table")

If answer = vbNo Then
  ws.[a4:r20000].Interior.Color = RGB(245, 252, 255)
  ws.[ae4:ae20000].Interior.Color = RGB(245, 252, 255)
  Exit Sub
End If

With ws
.[a1].UnMerge
.[a4:r20000].Interior.Color = RGB(245, 252, 255)
a = .Range("ad4:Ae" & .Cells(Rows.Count, "ae").End(xlUp).Row).Value 'AD AE Values store in array
End With

For Each ss In Range("a3:r3") 'Store Dict from range a3 to r3
    If ss.Value <> "" Then
        dict.Add ss.Value, ss.Column
    End If
Next ss

For i = 1 To UBound(a, 1)
    If dict.exists(a(i, 1)) Then
        Set c = Columns(dict(a(i, 1))).Find(what:=a(i, 2), LookIn:=xlValues)
      
        If c Is Nothing Then
            MsgBox a(i, 1) & " " & a(i, 2) & "cant be found"
            Cells(i + 3, "AE").Interior.Color = vbRed
        Else
            c.Interior.Color = RGB(255, 192, 0)
        End If
  
    End If
Next i

End Sub

 

[etaf]

or using conditional formatting

=COUNTIFS($AD$4:$AD$41,A$3,$AE$4:$AE$41,A4)>0

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
$A$4:$R$66 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=COUNTIFS($AD$4:$AD$41,A$3,$AE$4:$AE$41,A4)>0

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book2
	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	Z	AA	AB	AC	AD	AE	AF
1	SEATING MAP																			PARTICIPANTS' TABLES
2
3	A		B	C		D	E		F	G		H	I		J	K		L		PARTICIPANTS										TABLE
4				62			62			62			62			62				Person 1										A	58
5			61	61			61			61			61			61				Person 2										g	45
6			60	60		60	60		60	60		60	60		60	60		60		Person 3										c	62
7	59		59	59		59	59		59	59		59	59		59	59		59		P4										k	56
8	58		58	58		58	58		58	58		58	58		58	58		58
9	57		57	57		57	57		57	57		57	57		57	57		57
10	56			56			56			56			56			56
11	55		55	55		55	55		55	55		55	55		55	55		55
12	54		54	54		54	54		54	54		54	54		54	54		54
13	53		53	53		53	53		53	53		53	53		53	53		53
14	52		52	52		52	52		52	52		52	52		52	52		52
15				51			51			51			51			51
16	50		50	50		50	50		50	50		50	50		50	50		50
17	49		49	49		49	49		49	49		49	49		49	49		49
18	48		48	48		48	48		48	48		48	48		48	48		48
19	47		47	47		47	47		47	47		47	47		47	47		47
20	46			46			46			46			46			46
21	45		46	45		45	45		45	45		45	45		45	45		45
22	44		44	44		44	44		44	44		44	44		44	44		44
23	43		43	43		43	43		43	43		43	43		43	43		43
24			42	42		42			42	42		42	42		42	42		42
25
26	41			41			41			41			41			41
27	40			40			40			40			40			40
28	39		39	39		39	39		39	39		39	39		39	39		39
29	38		38	38		38	38		38	38		38	38		38	38		38
30	37		37	37		37	37		37	37		37	37		37	37		37
31	36		36	36		36	36		36	36		36	36		36	36		36
32	35			35			35			35			35			35
33	34		34	34		34	34		34	34		34	34		34	34		34
34	33		33	33		33	33		33	33		33	33		33	33		33
35	32		32	32		32	32		32	32		32	32		32	32		32
36	31		31	31		31	31		31	31		31	31		31	31		31
37	30			30			30			30			30			30
38	29		29	29		29	29		29	29		29	29		29	29		29
39	28		28	28		28	28		28	28		28	28		28	28		28
40	27		27	27		27	27		27	27		27	27		27	27		27
41	26		26	26		26	26		26	26		26	26		26	26		26
42
43	24
44	23
45	22
46	21			21			21			21			21			21
47	20			20			20			20			20			20
48	19		19	19		19	19		19	19		19	19		19	19		19
49	18		18	18		18	18		18	18		18	18		18	18		18
50	17		17	17		17	17		17	17		17	17		17	17		17
51	16		16	16		16	16		16	16		16	16		16	16		16
52				15			15			15			15			15
53	14		14	14		14	14		14	14		14	14		14	14		14
54	13		13	13		13	13		13	13		13	13		13	13		13
55	12		12	12		12	12		12	12		12	12		12	12		12
56	11		11	11		11	11		11	11		11	11		11	11		11
57	10			10			10			10			10			10
58	9		9	9		9	9		9	9		9	9		9	9		9
59	8		8	8		8	8		8	8		8	8		8	8		8
60	7		7	7		7	7		7	7		7	7
61	6		6	6					6	6		6	6
62			5	5						5			5
63	4		4	4		4	4		4	4		4	4
64	3		3	3		3	3		3	3		3	3
65	2		2	2		2	2		2	2		2	2
66	1		1	1		1	1		1	1		1	1
67
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:R66	Expression	=COUNTIFS($AD$4:$AD$41,A$3,$AE$4:$AE$41,A4)>0	text	NO

 

[RudRud]

FYR: For A1 Seating Map,

It would be good to unmerge cell, Perhaps you can select from a1 to r1 then go to format cells, change it into center across selection.

Using Merge Cells are definitely not flexible to work with, CMIIW @jennuine

 

[Peter_SSs]

Another possible Conditional formatting option

23 06 30.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	Z	AA	AB	AC	AD	AE	AF
1	SEATING MAP																			PARTICIPANTS' TABLES
2
3	A		B	C		D	E		F	G		H	I		J	K		L		PARTICIPANTS										TABLE
4				62			62			62			62			62				Person 1										A	58
5			61	61			61			61			61			61				Person 2										G	45
6			60	60		60	60		60	60		60	60		60	60		60		Person 3										K	56
7	59		59	59		59	59		59	59		59	59		59	59		59
8	58		58	58		58	58		58	58		58	58		58	58		58
9	57		57	57		57	57		57	57		57	57		57	57		57
10	56			56			56			56			56			56
11	55		55	55		55	55		55	55		55	55		55	55		55
12	54		54	54		54	54		54	54		54	54		54	54		54
13	53		53	53		53	53		53	53		53	53		53	53		53
14	52		52	52		52	52		52	52		52	52		52	52		52
15				51			51			51			51			51
16	50		50	50		50	50		50	50		50	50		50	50		50
17	49		49	49		49	49		49	49		49	49		49	49		49
18	48		48	48		48	48		48	48		48	48		48	48		48
19	47		47	47		47	47		47	47		47	47		47	47		47
20	46			46			46			46			46			46
21	45		46	45		45	45		45	45		45	45		45	45		45
22	44		44	44		44	44		44	44		44	44		44	44		44
23	43		43	43		43	43		43	43		43	43		43	43		43
24			42	42		42			42	42		42	42		42	42		42
25
26	41			41			41			41			41			41
Seating

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:R66	Expression	=AND(A4<>"",MATCH(A$3&A4,$AD$4:$AD$999&$AE$4:$AE$999,0))	text	NO

 

[jennuine]

Hello everyone,

The COUNTIFS conditional formatting worked!! Thank you very much for all your help!

 

[Peter_SSs]

Glad you got something that worked. Thanks for the follow-up.

 

[etaf]

you are welcome