Conditional Formatting of Green vs. Green/White, etc; Diagonal Border Not Possible

[TheWaterbug]

We are using Ethernet cabling for a non-Ethernet application, and crimping the wires into a giant 96-pin connector. The internal wires in a standard Ethernet cable are colored Orange/White, Orange, Green/White, etc., where each Color and Color/White pair carries a differential signal, so each pair of wires should be kept physically proximate.

We have the customer's pin assignments in an Excel sheet (heavily sanitized, obviously), and I'm trying to map that columnar table on the left into a grid on the right that matches the physical layout of the 96-pin connector, so that the poor soul crimping the wires has a visual reference:

The mini-sheet inserted below is only about 1/3 of the table--the diagonal border and the "pattern" in the conditional formatting do not render in the forum, which is why I have the screen capture above. The full .xlsx is linked above:

Cat8_96Pin.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	AG
1	Count	Signal Name	Grid	Cable Jacket	RJ45 Pin	Wire Color
2	1	Signal1_H	A1	Black_1	1	Org/White
3	2	Signal1_L	B1	Black_1	2	Orange		J1 Connector Map
4	3	Signal2_H	C1	Black_1	3	Grn/White			1			2			3			4			5			6			7			8
5	4	Signal2_L	D1	Black_1	6	Green			Signal	Jacket	Wire	Signal	Jacket	Wire	Signal	Jacket	Wire	Signal	Jacket	Wire	Signal	Jacket	Wire	Signal	Jacket	Wire	Signal	Jacket	Wire	Signal	Jacket	Wire
6	5	Signal3_H	E1	Black_1	4	Blue		A	Signal1_H	Black_1	Org/White	Signal5_H	Blue_1	Org/White	Signal11_H	Green_1	Org/White	Signal20_H	Purple_1	Org/White	Signal23_H	Red_1	Org/White	Signal8_H	Gray_1	Org/White	Signal28_H	White_1	Brn/White	Signal29_H	Yellow_1	Org/White	A
7	6	Signal3_L	F1	Black_1	5	Blue/White		B	Signal1_L	Black_1	Orange	Signal5_L	Blue_1	Orange	Signal11_L	Green_1	Orange	Signal20_L	Purple_1	Orange	Signal23_L	Red_1	Orange	Signal8_L	Gray_1	Orange	Signal28_L	White_1	Brown	Signal29_L	Yellow_1	Orange	B
8	7	Signal4_H	G1	Black_1	7	Brn/White		C	Signal2_H	Black_1	Grn/White	Signal6_H	Blue_1	Grn/White	Signal12_H	Green_1	Grn/White	Signal21_H	Purple_1	Grn/White	Signal24_H	Red_1	Grn/White	Signal9_H	Gray_1	Grn/White	Signal32_H	White_1	Org/White	Signal30_H	Yellow_1	Grn/White	C
9	8	Signal4_L	H1	Black_1	8	Brown		D	Signal2_L	Black_1	Green	Signal6_L	Blue_1	Green	Signal12_L	Green_1	Green	Signal21_L	Purple_1	Green	Signal24_L	Red_1	Green	Signal9_L	Gray_1	Green	Signal32_L	White_1	Orange	Signal30_L	Yellow_1	Green	D
10	9	Signal5_H	A2	Blue_1	1	Org/White		E	Signal3_H	Black_1	Blue	Signal7_H	Blue_1	Blue	Signal13_H	Green_1	Blue	Signal22_H	Purple_1	Blue	Signal25_H	Red_1	Blue	Signal16_H	Gray_1	Blue	Signal33_H	White_1	Grn/White	Signal31_H	Yellow_1	Blue	E
11	10	Signal5_L	B2	Blue_1	2	Orange		F	Signal3_L	Black_1	Blue/White	Signal7_L	Blue_1	Blue/White	Signal13_L	Green_1	Blue/White	Signal22_L	Purple_1	Blue/White	Signal25_L	Red_1	Blue/White	Signal16_L	Gray_1	Blue/White	Signal33_L	White_1	Green	Signal31_L	Yellow_1	Blue/White	F
12	11	Signal6_H	C2	Blue_1	3	Grn/White		G	Signal4_H	Black_1	Brn/White	Signal10_H	Blue_1	Brn/White	Signal14_H	Green_1	Brn/White	Signal26_H	Purple_1	Brn/White	Signal27_H	Red_1	Brn/White	Signal17_H	Gray_1	Brn/White	Signal34_H	White_1	Blue	Signal35_H	Yellow_1	Brn/White	G
13	12	Signal6_L	D2	Blue_1	6	Green		H	Signal4_L	Black_1	Brown	Signal10_L	Blue_1	Brown	Signal14_L	Green_1	Brown	Signal26_L	Purple_1	Brown	Signal27_L	Red_1	Brown	Signal17_L	Gray_1	Brown	Signal34_L	White_1	Blue/White	Signal35_L	Yellow_1	Brown	H
14	13	Signal7_H	E2	Blue_1	4	Blue		J	Signal15_H	Orange_1	Org/White	Signal36_H	Black_2	Org/White	Signal38_H	Black_2	Blue	Signal42_H	Gray_2	Org/White	Signal44_H	Gray_2	Blue	Signal46_H	Green_2	Org/White	Signal48_H	Green_2	Blue	Signal50_H	Orange_2	Org/White	J
15	14	Signal7_L	F2	Blue_1	5	Blue/White		K	Signal15_L	Orange_1	Orange	Signal36_L	Black_2	Orange	Signal38_L	Black_2	Blue/White	Signal42_L	Gray_2	Orange	Signal44_L	Gray_2	Blue/White	Signal46_L	Green_2	Orange	Signal48_L	Green_2	Blue/White	Signal50_L	Orange_2	Orange	K
16	15	Signal8_H	A6	Gray_1	1	Org/White		L	Signal18_H	Orange_1	Grn/White	Signal37_H	Black_2	Grn/White	Signal39_H	Black_2	Brn/White	Signal43_H	Gray_2	Grn/White	Signal45_H	Gray_2	Brn/White	Signal47_H	Green_2	Grn/White	Signal49_H	Green_2	Brn/White	Signal51_H	Orange_2	Grn/White	L
17	16	Signal8_L	B6	Gray_1	2	Orange		M	Signal18_L	Orange_1	Green	Signal37_L	Black_2	Green	Signal39_L	Black_2	Brown	Signal43_L	Gray_2	Green	Signal45_L	Gray_2	Brown	Signal47_L	Green_2	Green	Signal49_L	Green_2	Brown	Signal51_L	Orange_2	Green	M
18	17	Signal9_H	C6	Gray_1	3	Grn/White		N	Signal19_H	Orange_1	Blue	Signal40_H	Blue_2	Org/White	Signal41_H	Blue_2	Grn/White																N
19	18	Signal9_L	D6	Gray_1	6	Green		P	Signal19_L	Orange_1	Blue/White	Signal40_L	Blue_2	Orange	Signal41_L	Blue_2	Green																P
20	19	Signal10_H	G2	Blue_1	7	Brn/White			1			2			3			4			5			6			7			8
21	20	Signal10_L	H2	Blue_1	8	Brown
LVDS

Cell Formulas
Range	Formula
I6:I19,L6:L19,O6:O19,R6:R19,U6:U19,X6:X19,AA6:AA19,AD6:AD19	I6	=IF(ISERROR(MATCH($H6&I$4,$C$2:$C$103,0)),"",INDEX($B$2:$B$103,MATCH($H6&I$4,$C$2:$C$103,0)))
J6:J19,M6:M19,P6:P19,S6:S19,V6:V19,Y6:Y19,AB6:AB19,AE6:AE19	J6	=IF(ISERROR(MATCH($H6&I$4,$C$2:$C$103,0)),"",INDEX($D$2:$D$103,MATCH($H6&I$4,$C$2:$C$103,0)))
K6:K19,N6:N19,Q6:Q19,T6:T19,W6:W19,Z6:Z19,AC6:AC19,AF6:AF19	K6	=IF(ISERROR(MATCH($H6&I$4,$C$2:$C$103,0)),"",INDEX($F$2:$F$103,MATCH($H6&I$4,$C$2:$C$103,0)))
A3:A21	A3	=A2+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K6:K19,N6:N19,Q6:Q19,T6:T19,W6:W19,Z6:Z19,AF6:AF19,AC6:AC19	Expression	=K6="Org/White"	text	NO
K6:K19,N6:N19,Q6:Q19,T6:T19,W6:W19,Z6:Z19,AF6:AF19,AC6:AC19	Expression	=K6 ="Orange"	text	NO
K6:K19,N6:N19,Q6:Q19,T6:T19,W6:W19,Z6:Z19,AF6:AF19,AC6:AC19	Expression	=K6 ="Grn/White"	text	NO
K6:K19,N6:N19,Q6:Q19,T6:T19,W6:W19,Z6:Z19,AF6:AF19,AC6:AC19	Expression	=K6 ="Green"	text	NO
K6:K19,N6:N19,Q6:Q19,T6:T19,W6:W19,Z6:Z19,AF6:AF19,AC6:AC19	Expression	=K6 ="Blue"	text	NO
K6:K19,N6:N19,Q6:Q19,T6:T19,W6:W19,Z6:Z19,AF6:AF19,AC6:AC19	Expression	=K6 ="Blue/White"	text	NO
K6:K19,N6:N19,Q6:Q19,T6:T19,W6:W19,Z6:Z19,AF6:AF19,AC6:AC19	Expression	=K6="Brn/White"	text	NO
K6:K19,N6:N19,Q6:Q19,T6:T19,W6:W19,Z6:Z19,AF6:AF19,AC6:AC19	Expression	=K6 ="Brown"	text	NO
J6:K19,M6:N19,P6:Q19,S6:T19,V6:W19,Y6:Z19,AE6:AF19,AB6:AC19	Expression	=LEFT(J6,FIND("_",J6)-1)="White"	text	NO
J6:K19,M6:N19,P6:Q19,S6:T19,V6:W19,Y6:Z19,AE6:AF19,AB6:AC19	Expression	=LEFT(J6,FIND("_",J6)-1)="Yellow"	text	NO
J6:K19,M6:N19,P6:Q19,S6:T19,V6:W19,Y6:Z19,AE6:AF19,AB6:AC19	Expression	=LEFT(J6,FIND("_",J6)-1)="Red"	text	NO
J6:K19,M6:N19,P6:Q19,S6:T19,V6:W19,Y6:Z19,AE6:AF19,AB6:AC19	Expression	=LEFT(J6,FIND("_",J6)-1)="Purple"	text	NO
J6:K19,M6:N19,P6:Q19,S6:T19,V6:W19,Y6:Z19,AE6:AF19,AB6:AC19	Expression	=LEFT(J6,FIND("_",J6)-1)="Orange"	text	NO
J6:K19,M6:N19,P6:Q19,S6:T19,V6:W19,Y6:Z19,AE6:AF19,AB6:AC19	Expression	=LEFT(J6,FIND("_",J6)-1)="Green"	text	NO
J6:K19,M6:N19,P6:Q19,S6:T19,V6:W19,Y6:Z19,AE6:AF19,AB6:AC19	Expression	=LEFT(J6,FIND("_",J6)-1)="Gray"	text	NO
J6:K19,M6:N19,P6:Q19,S6:T19,V6:W19,Y6:Z19,AE6:AF19,AB6:AC19	Expression	=LEFT(J6,FIND("_",J6)-1)="Black"	text	NO
J6:K19,M6:N19,P6:Q19,S6:T19,V6:W19,Y6:Z19,AE6:AF19,AB6:AC19	Expression	=LEFT(J6,FIND("_",J6)-1)="Blue"	text	NO
C2:C103	Cell Value	duplicates	text	NO

When the user enters a Grid value in the left table, the lookup functions in the right table pick up signal name, the cable jacket color, and wire colors with conditional formatting. But diagonal borders are not available in Conditional Formatting, and the horizontal stripes look absolutely terrible. I've tried all the other available patterns, and they all look similarly horrible.

The white diagonal borders in the left table are statically assigned, and that's what I'd prefer, if it were possible.

I need everything in the right table to be the results of formulas, including the formatting, because I need it to update if/when the user updates the Grid assignments in the left table.

Can anyone suggest a different way to conditionally format my Color/White cells in the right table so that they're legible, but still carry the visual impact of the Color/White like the diagonal border would have done? Or a completely different solution that accomplishes the same goal?

VBA is not allowed because we will be sending this document back and forth with the customer.

Thanks!

 

[TheWaterbug]

I can enlarge mine in Windows.

Hmmm. On my two workstations I'm using Home and Student 2019 and Professional Plus 2021, and neither allows a resize. Maybe it's a 365 thing.

 

[MARK858]

It is a 365 thing