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!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Would this be any good for the Org/White?

If so, this is how I achieved it in Conditional Formatting:

 

[TheWaterbug]

C'est parfait! I didn't see this because gradient fill isn't available on macOS

Fortunately I have access to both Mac and Windows, and the rest of the team is on Windows, so I can use this as long as I remember to check everything I do in Windows before I distribute.

Thanks!!!

 

[TheWaterbug]

Looks great!!! Oddly enough if I apply this Format/Conditional Format in Excel/Win and then open it in Excel/Mac, it does render properly; I just can't create it in Excel/Mac:

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.
I don't have a Mac & wasn't aware some of those options are not available on Mac. Good that it works in both systems once set up though.

 

[jolivanes]

You can color cells half/half with two different colored triangles that are sized to fit within a cell.
However, I don't know if you could copy/paste them with CF if you had them in another sheet as template.
Peter would know if that is possible.
A few years ago there was a thread where it was done by macro.

 

[Peter_SSs]

I don't know if you could copy/paste them with CF if you had them in another sheet as template.

CF could not manipulate shapes on the worksheet.
In any case the above direct CF solution looks pretty good to me (other than that perhaps those cells with the white diagonal shading as I suggested that currently have white font might need a black/dark font instead). Don't know if it will matter to the OP but to me this looks a bit easier to read.

 

[TheWaterbug]

CF could not manipulate shapes on the worksheet.
In any case the above direct CF solution looks pretty good to me (other than that perhaps those cells with the white diagonal shading as I suggested that currently have white font might need a black/dark font instead). Don't know if it will matter to the OP but to me this looks a bit easier to read.

View attachment 94039

Yes, I changed all the fonts to black!

It looks way better than my original idea with the diagonal border.

I just wish it were a bit easier to create CF rules, e.g. if there were a "copy rule" button. At least in Excel/Mac I can enlarge the CF dialog box and see them all at once:

vs.

But Excel/Mac doesn't have the "Apply" button, so I can't see how it will look on the whole sheet without OKing and then re-opening the dialog if I want to change things.

I wish Microsoft would just make it look and work the same way on both platforms, like they did way back in Excel 4.0. And get off my lawn!!!!

 

[jolivanes]

I think you're right. Peter's solution looks quite a bit better then diagonal triangles would look.
Nice.

 

[Peter_SSs]

At least in Excel/Mac I can enlarge the CF dialog box and see them all at once:

I can enlarge mine in Windows.