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

David77

Board Regular
Joined
Jun 24, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • 1596716868653.png
    1596716868653.png
    52.2 KB · Views: 2

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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.
Untitled2.jpg

Untitled.jpg

Book1
ABCDE
1NumberNameLocationcodeserial
245ankitindia788
340punitindia784
4785nehaindia781
5987rahulindia784
6452riyaindia7885
7456biswaindia741
82254abhiindia756
9789655sapnaindia742
1078522ranjitindia741
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:E10Expression=NOT(ISBLANK($E1))textNO
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1NumberNameLocationcodeserial
245ankitindia788
340punitindia784
4785nehaindia781
5987rahulindia784
6452riyaindia7885
7456biswaindia741
82254abhiindia756
9789655sapnaindia742
1078522ranjitindia741
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:E10Expression=NOT(ISBLANK($E1))textNO
It worked!!!

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

1596780213776.png


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

Board Regular
Joined
Jul 25, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1NumberNameLocationcodeserial
245ankitindia7880
340punitindia784
4785nehaindia7815
5987rahulindia784
6452riyaindia7885
7456biswaindia741
82254abhiindia7560
9789655sapnaindia742
1078522ranjitindia741
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:E10Expression=$E2>0textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,108,654
Messages
5,524,114
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top