Check if certain combinations are present in certain columns

[BGerm]

I want to check if these combinatios are present in an excel sheet for each Father, Mother and their Child. If they are highlight them, or bold, or in an empty column, in same row, write error.
Each Mother,Father, Child separated by an empy column are separate case.

I am beginner in VBA, thank you for suggestions in advance.

My data looks like this

child

mother

father

child

mother

father

child

mother

father

Chr

Start

End

Ref

Alt

1​

2​

3​

4​

5​

6​

8​

9​

10​

male

female

male

female

female

male

male

female

male

1​

3775283​

3775283​

T

G

1/1/

1/1/

0/0

1/1/

1/1/

0/0

0/0

0/0

0/0

1​

3782273​

3782273​

C

A

1/1/

0/0

0/0

0/0

0/0

0/0

0/0

0/0

0/0

1​

3782307​

3782307​

C

G

0/0

0/0

0/0

1/1/

NaN

0/0

0/1

0/0

1/1/

1​

3782524​

3782524​

C

T

0/1

0/0

1/1/

0/0

0/0

0/0

1/1/

0/0

0/0

1​

3782550​

3782550​

C

T

0/0

0/0

0/0

NaN

1/1/

0/0

0/0

0/0

0/0

1​

3784600​

3784600​

C

T

NaN

1/1/

0/0

0/0

0/0

0/0

1/1/

1/1/

0/0

1​

3786165​

3786165​

C

T

0/0

0/0

0/0

0/1

0/0

1/1/

0/0

0/0

0/0

1​

3786185​

3786185​

C

T

0/0

0/0

0/0

0/0

0/0

0/0

NaN

1/1/

0/0

1​

3786189​

3786189​

G

A

1/1/

1/1/

0/1

1/1/

1/1/

0/1

0/0

0/1

0/1

1​

3786228​

3786228​

C

T

0/0

0/0

0/0

0/0

0/0

0/0

0/0

0/0

0/0

1​

3786245​

3786245​

G

A

1/1/

NaN

0/0

1/1/

0/0

0/0

1/1/

NaN

0/0

1​

3786275​

3786275​

A

G

0/0

0/0

0/0

0/0

0/0

0/0

1/1/

0/0

0/0

1​

3789026​

3789026​

T

G

1/1/

NaN

0/0

1/1/

NaN

0/0

0/0

0/0

0/0

1​

3789061​

3789061​

T

C

NaN

1/1/

0/0

0/0

0/0

0/0

0/0

0/0

0/0

For these combinations I am checking:

Father	Mother	Child
0/0	0/0	1/1/
0/0	0/0	0/1
0/0	0/1	1/1/
0/0	1/1/	1/1/
0/0	1/1/	0/0
0/1	0/0	1/1/
0/1	1/1/	0/0
1/1/	0/0	0/0
1/1/	0/0	1/1/
1/1/	0/1	0/0
1/1/	1/1/	0/1
1/1/	1/1/	0/0
NaN	0/0	1/1/
NaN	1/1/	0/0
0/0	NaN	1/1/
1/1/	NaN	0/0

 

[J.Ty.]

Hi,

Welcome to the Board!

Is the following what you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1							child	mother	father		child	mother	father		child	mother	father
2	Chr	Start	End	Ref	Alt		1	2	3		4	5	6		8	9	10
3							male	female	male		female	female	male		male	female	male
4
5
6	1	3775283	3775283	T	G		1/1/	1/1/	0/0		1/1/	1/1/	0/0		0/0	0/0	0/0
7	1	3782273	3782273	C	A		1/1/	0/0	0/0		0/0	0/0	0/0		0/0	0/0	0/0
8	1	3782307	3782307	C	G		0/0	0/0	0/0		1/1/	NaN	0/0		0/1	0/0	1/1/
9	1	3782524	3782524	C	T		0/1	0/0	1/1/		0/0	0/0	0/0		1/1/	0/0	0/0
10	1	3782550	3782550	C	T		0/0	0/0	0/0		NaN	1/1/	0/0		0/0	0/0	0/0
11	1	3784600	3784600	C	T		NaN	1/1/	0/0		0/0	0/0	0/0		1/1/	1/1/	0/0
12	1	3786165	3786165	C	T		0/0	0/0	0/0		0/1	0/0	1/1/		0/0	0/0	0/0
13	1	3786185	3786185	C	T		0/0	0/0	0/0		0/0	0/0	0/0		NaN	1/1/	0/0
14	1	3786189	3786189	G	A		1/1/	1/1/	0/1		1/1/	1/1/	0/1		0/0	0/1	0/1
15	1	3786228	3786228	C	T		0/0	0/0	0/0		0/0	0/0	0/0		0/0	0/0	0/0
16	1	3786245	3786245	G	A		1/1/	NaN	0/0		1/1/	0/0	0/0		1/1/	NaN	0/0
17	1	3786275	3786275	A	G		0/0	0/0	0/0		0/0	0/0	0/0		1/1/	0/0	0/0
18	1	3789026	3789026	T	G		1/1/	NaN	0/0		1/1/	NaN	0/0		0/0	0/0	0/0
19	1	3789061	3789061	T	C		NaN	1/1/	0/0		0/0	0/0	0/0		0/0	0/0	0/0
20
21
22	Father	Mother	Child
23	0/0	0/0	1/1/
24	0/0	0/0	0/1
25	0/0	0/1	1/1/
26	0/0	1/1/	1/1/
27	0/0	1/1/	0/0
28	0/1	0/0	1/1/
29	0/1	1/1/	0/0
30	1/1/	0/0	0/0
31	1/1/	0/0	1/1/
32	1/1/	0/1	0/0
33	1/1/	1/1/	0/1
34	1/1/	1/1/	0/0
35	NaN	0/0	1/1/
36	NaN	1/1/	0/0
37	0/0	NaN	1/1/
38	1/1/	NaN	0/0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O6:Q19	Expression	=COUNTIFS($A$23:$A$38;O6;$B$23:$B$38;P6;$C$23:$C$38;Q6)>0	text	NO
K6:M19	Expression	=COUNTIFS($A$23:$A$38;K6;$B$23:$B$38;L6;$C$23:$C$38;M6)>0	text	NO
G6:I19	Expression	=COUNTIFS($A$23:$A$38;G6;$B$23:$B$38;H6;$C$23:$C$38;I6)>0	text	NO

 

[BGerm]

My data frame is much bigger than one I posted here, so I do not know is it hand to write for 245 trio such formatting, if I got your approach right.

 

[J.Ty.]

OK. I have now applied the same conditional formatting to all cells in the area and it works precisely the same way. So you can enter it for all 245 families(?) in one step or make for just one cell (I did it for G6) and then use format painter to apply it to all of them in one step.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1							child	mother	father		child	mother	father		child	mother	father
2	Chr	Start	End	Ref	Alt		1	2	3		4	5	6		8	9	10
3							male	female	male		female	female	male		male	female	male
4
5
6	1	3775283	3775283	T	G		1/1/	1/1/	0/0		1/1/	1/1/	0/0		0/0	0/0	0/0
7	1	3782273	3782273	C	A		1/1/	0/0	0/0		0/0	0/0	0/0		0/0	0/0	0/0
8	1	3782307	3782307	C	G		0/0	0/0	0/0		1/1/	NaN	0/0		0/1	0/0	1/1/
9	1	3782524	3782524	C	T		0/1	0/0	1/1/		0/0	0/0	0/0		1/1/	0/0	0/0
10	1	3782550	3782550	C	T		0/0	0/0	0/0		NaN	1/1/	0/0		0/0	0/0	0/0
11	1	3784600	3784600	C	T		NaN	1/1/	0/0		0/0	0/0	0/0		1/1/	1/1/	0/0
12	1	3786165	3786165	C	T		0/0	0/0	0/0		0/1	0/0	1/1/		0/0	0/0	0/0
13	1	3786185	3786185	C	T		0/0	0/0	0/0		0/0	0/0	0/0		NaN	1/1/	0/0
14	1	3786189	3786189	G	A		1/1/	1/1/	0/1		1/1/	1/1/	0/1		0/0	0/1	0/1
15	1	3786228	3786228	C	T		0/0	0/0	0/0		0/0	0/0	0/0		0/0	0/0	0/0
16	1	3786245	3786245	G	A		1/1/	NaN	0/0		1/1/	0/0	0/0		1/1/	NaN	0/0
17	1	3786275	3786275	A	G		0/0	0/0	0/0		0/0	0/0	0/0		1/1/	0/0	0/0
18	1	3789026	3789026	T	G		1/1/	NaN	0/0		1/1/	NaN	0/0		0/0	0/0	0/0
19	1	3789061	3789061	T	C		NaN	1/1/	0/0		0/0	0/0	0/0		0/0	0/0	0/0
20
21
22	Father	Mother	Child
23	0/0	0/0	1/1/
24	0/0	0/0	0/1
25	0/0	0/1	1/1/
26	0/0	1/1/	1/1/
27	0/0	1/1/	0/0
28	0/1	0/0	1/1/
29	0/1	1/1/	0/0
30	1/1/	0/0	0/0
31	1/1/	0/0	1/1/
32	1/1/	0/1	0/0
33	1/1/	1/1/	0/1
34	1/1/	1/1/	0/0
35	NaN	0/0	1/1/
36	NaN	1/1/	0/0
37	0/0	NaN	1/1/
38	1/1/	NaN	0/0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G6:Q19	Expression	=COUNTIFS($A$23:$A$38;G6;$B$23:$B$38;H6;$C$23:$C$38;I6)>0	text	NO