Conditionally format cells to red color if cell 4 rows to the left is not blank

[David77]

Hello everybody,



I currently have the following list:







I want to create a formula (probably through conditional formatting i'd imagine), that basically says:



If the cell 4 turns to the left of any designated cell (A1) in row E is not = blank, color the cell in row E with a red background.



As you can see from my list, I currently have a list of data which is pulled from a hidden sheet in the background for cell rows A, B, C & D. However, while these update automatically (and very nicely so too!), the red row with a red background filling does unfortunately not.



Can anybody help me in solving this issue? I would greatly appreciate it!



Thank you so much everybody !



Best regards,

David

 

[Toadstool]

Hi David77,

Is this what you want?

Book1
	A	B	C	D	E
117	AX30166	Pin 1.5x11mm (6pcs)	4	K3009649
118	AX30190	Shaft 5x18 (2pcs.)	7	K3002961
119	AX30198	Slipper Drive Gear Shaft 5x53.5	6	K3004392
120		Axle 5x35mm (2pcs.)	12	K3002985
121	AX30385	Diff Gasket 16x25x0.5mm (2pcs)	3	K3002992
122	AX30392	Bevel Gear Set (38/13)	12	K3003005
123		20T Drive Gear	5	K3003739
124		Input Shaft 5x29mm (2pcs)	8	K3009663
125	AX30410	Inner Slipper Plate	10	K3004408
126	AX30411	Outer Slipper Plate	10	K3004415
127	AX30412	Slipper pad 27x8x1mm	10	K3004422
128	AX30413	Slipper spring 8.5x12 165 lbs/in - Black (2pcs)	5	K3004439
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E117:E134	Expression	=A117<>""	text	NO

 

[David77]

Hi David77,

Is this what you want?

Book1
	A	B	C	D	E
117	AX30166	Pin 1.5x11mm (6pcs)	4	K3009649
118	AX30190	Shaft 5x18 (2pcs.)	7	K3002961
119	AX30198	Slipper Drive Gear Shaft 5x53.5	6	K3004392
120		Axle 5x35mm (2pcs.)	12	K3002985
121	AX30385	Diff Gasket 16x25x0.5mm (2pcs)	3	K3002992
122	AX30392	Bevel Gear Set (38/13)	12	K3003005
123		20T Drive Gear	5	K3003739
124		Input Shaft 5x29mm (2pcs)	8	K3009663
125	AX30410	Inner Slipper Plate	10	K3004408
126	AX30411	Outer Slipper Plate	10	K3004415
127	AX30412	Slipper pad 27x8x1mm	10	K3004422
128	AX30413	Slipper spring 8.5x12 165 lbs/in - Black (2pcs)	5	K3004439
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E117:E134	Expression	=A117<>""	text	NO

Awesome!!!

Thank you so much! Have a great day

Kind regards,
David