SOLVED

If a cell contain a specific text then remove text border

Copper Contributor

Hello, 

In my excel file, which is a template, my respondent has to make a choice between 3 options with a dropdown list. When he selects an element, it returns to cell D2 the element. For instance, if I select "Product Range" in the dropdown menu, it appears "Product Range" in D2.

MedhiRC_1-1672232760005.png

 

But, depending on this data, I would like that conditional element appears/ disappear. If you select Cost Center --> Name is hidden, if you selected Product Range --> Code is hidden. (formula :  =IF($D$2="Product Range";"Name";""). But I would like to remove the border from cell E5 (it looks like a text box). Indeed, if Name is empty, the text box should be hidden. 

MedhiRC_0-1672232611088.png

How can I add a conditional format to borders? Or maybe, there is a workaround? 

 

Thank you for your help, and have a nice day! 

 

6 Replies
best response confirmed by Medhi-RC (Copper Contributor)
Solution

@Medhi-RC 

Select the cell with the formula that returns Name.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=$D$2<>"Product Range"

 

Click Format...

Activate the Border tab.

Click the None button.

S2077.png

Click OK, then click OK again.

 

Do the same for the cell with the formula that returns Code, but with the formula

 

=$D$2<>"Cost Center"

@Medhi-RC 

A slight variant that I used to use, is to format the input boxes as if they were each in use, but then use the conditional format to reset the format to background if the string does not match the required value.  My taste for colour has probably become more restrained over the years, but the image illustrates the type of effect one can achieve.

image.png

Thank you very much for your answer! But, if there are more than 2 elements how can I put more options? For instance, if we have chosen "Partner entity" or "Cost center" I would like to remove it. Is there any "or" function?
I am going to use it, it's working well. :)

Nice idea, I understand it. It could be a nice option too, thank you very much for answering!

@Medhi-RC 

The rules that I proposed will remove the borders if the user selects ANY other item than the intended one.

Ok it's noted, thank you.
1 best response

Accepted Solutions
best response confirmed by Medhi-RC (Copper Contributor)
Solution

@Medhi-RC 

Select the cell with the formula that returns Name.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=$D$2<>"Product Range"

 

Click Format...

Activate the Border tab.

Click the None button.

S2077.png

Click OK, then click OK again.

 

Do the same for the cell with the formula that returns Code, but with the formula

 

=$D$2<>"Cost Center"

View solution in original post