Transpose cell highlighting

[pthwaites]

Hi all,

I have a data sheet containing two correlation matrices. In the sample attached, the matrix on the left shows the Pearson correlations (r), and on the right is the p value (i.e. the statistical significance of the corresponding Pearson correlation). In the sheet on the right, I've used a cell highlighting rule to flag all of the cells which have a p < .05. I would like to transpose ONLY THE CELL HIGHLIGHTING from the right matrix to the left one. That is, if a box is coloured red in the right sheet, I want it also to be coloured red in the left sheet; but I don't want the numerical value in either sheet to be changed. For example, in the example below, the only red cell is H7, which shows p=0.0067 between P.EFSET.Total and VLT.5. The corresponding correlation score in the left side is 0.89. I want this cell to become red, but I don't want its value to change (note: in the actual sheet, there are many more rows and columns, with many more red-highlighted values).

NB. I realise that a simple workaround would be to create a new rule for the left sheet, highlighting values of e.g. r > .8. But this would not be an accurate solution to the problem because the p values are not solely determined by the size of r.

Sample correlation matrices.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		r.EFSET.Total	r.VLT.1	r.VLT.2	r.VLT.3	r.VLT.4	r.VLT.5	p.EFSET.Total	p.VLT.1	p.VLT.2	p.VLT.3	p.VLT.4	p.VLT.5
2	EFSET.Total	1						0
3	VLT.1	0.48	1					0.28	0
4	VLT.2	0.23	-0.12	1				0.62	0.79	0
5	VLT.3	0.33	0.68	0.23	1			0.47	0.092	0.62	0
6	VLT.4	0.47	0.14	0.58	0.51	1		0.28	0.76	0.17	0.25	0
7	VLT.5	0.89	0.68	0.16	0.48	0.47	1	0.0067	0.095	0.73	0.27	0.28	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1:M7	Cell Value	=0	text	NO
H2:M7	Cell	contains a blank value	text	NO
H2:M7	Cell	contains a blank value	text	NO
H2:M7	Cell Value	<0.05	text	NO

 

[aRandomHelper]

Try applying a custom formula conditional formatting:

B
	B	C	D	E	F	G
1	r.EFSET.Total	r.VLT.1	r.VLT.2	r.VLT.3	r.VLT.4	r.VLT.5
2	1
3	0.48	1
4	0.23	-0.12	1
5	0.33	0.68	0.23	1
6	0.47	0.14	0.58	0.51	1
7	0.89	0.68	0.16	0.48	0.47	1
Sheet16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:G7	Expression	=AND(H1>0,H1<0.05)	text	NO

 

[pthwaites]

Try applying a custom formula conditional formatting:

B
	B	C	D	E	F	G
1	r.EFSET.Total	r.VLT.1	r.VLT.2	r.VLT.3	r.VLT.4	r.VLT.5
2	1
3	0.48	1
4	0.23	-0.12	1
5	0.33	0.68	0.23	1
6	0.47	0.14	0.58	0.51	1
7	0.89	0.68	0.16	0.48	0.47	1
Sheet16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:G7	Expression	=AND(H1>0,H1<0.05)	text	NO

Perfect, thanks very much!