Change borders/gridlines of cells in rows A, B, C & D when cells in row E contains no blanks

[David77]

Hello everybody,

I am currently sitting with the following material overview file in Excel:



I have made some conditional formatting so whenever I enter a number in column E the entire row gets highlighted. Unfortunately, this erases/makes invisible my dark gridlines/borders from before. Naturally, I can (as you can see from the photo) create a conditional formatting for row E so whenever the value is not blank, it creates the dark gridline/border that I want (see the photo with the number 5 as an example).

However, I am not sure as to how I would go about with doing this for row A, B, C and D. Can somebody please help me with this?

P.S. I am not covering all of row A, B, C, D and E. It's only the values 36:495 for all of them in this list.

Thank you so much for your time everybody. I truly appreciate it!

Best regards,

David

 

[ER_Neha]

Hello. I tried solving your problem but I have one error, if it can be accepted on your side the formula is good to go. i.e. the error is the row which contains the number in E column will format the entire row with the same color as the row containing the header.
So the process is to select "new rule" under conditional formatting tab, and then select "use a formula to determine which cell to format", then define the rule as "=NOT(ISBLANK($E1))" then select border outline and also fill color.

Book1
	A	B	C	D	E
1	Number	Name	Location	code	serial
2	45	ankit	india	788
3	40	punit	india	784
4	785	neha	india	781
5	987	rahul	india	784
6	452	riya	india	788	5
7	456	biswa	india	741
8	2254	abhi	india	756
9	789655	sapna	india	742
10	78522	ranjit	india	741
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E10	Expression	=NOT(ISBLANK($E1))	text	NO

 

[David77]

He

Hello. I tried solving your problem but I have one error, if it can be accepted on your side the formula is good to go. i.e. the error is the row which contains the number in E column will format the entire row with the same color as the row containing the header.
So the process is to select "new rule" under conditional formatting tab, and then select "use a formula to determine which cell to format", then define the rule as "=NOT(ISBLANK($E1))" then select border outline and also fill color.
View attachment 19805
View attachment 19806

Book1
	A	B	C	D	E
1	Number	Name	Location	code	serial
2	45	ankit	india	788
3	40	punit	india	784
4	785	neha	india	781
5	987	rahul	india	784
6	452	riya	india	788	5
7	456	biswa	india	741
8	2254	abhi	india	756
9	789655	sapna	india	742
10	78522	ranjit	india	741
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E10	Expression	=NOT(ISBLANK($E1))	text	NO

It worked!!!

Thank you so much! This is how I set it up btw (top one amongst the conditional formattings):

May I ask, if you have the spare time, would there be a way to augment the formula, so it does exactly the same as before, but also includes the formatting if the cell in row E is not blank AND not equal to 0 as well?

Thank you so much for your time!

Best regards,
David

 

[ER_Neha]

Hello, glad I could help. So if the E column contains number then the much easier way is to set "=$E>0", the formula gets the speed too in this case.

Book1
	A	B	C	D	E
1	Number	Name	Location	code	serial
2	45	ankit	india	788	0
3	40	punit	india	784
4	785	neha	india	781	5
5	987	rahul	india	784
6	452	riya	india	788	5
7	456	biswa	india	741
8	2254	abhi	india	756	0
9	789655	sapna	india	742
10	78522	ranjit	india	741
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E10	Expression	=$E2>0	text	NO