Double border work-around in conditional formatting

[Musicman72]

Hello all - I'm trying to get double borders on an excel spreadsheet that uses conditional formatting on each row. I know that excel doesn't support changing border types to double via conditional formatting because it would affect the row size, so what I tried to do is format all of the cells with the double border by default, and then use conditional formatting to "un-border" the ones I don't want.

The format I'm looking for will have some rows with no borders (either blank values or a "0" in a hidden reference cell) and some will have just a single border on the top of the row (if "1" in the reference cell). The remaining rows will default to their original format, which is a single border on top and a double border on the bottom. The issue I'm having is that the no-border condition wipes out the top border on the rows where the reference is "1" because the "blank border" row abuts the next row with a single border on the top. It also wipes out the default formatting of the top border on the cells with both the top single border and the double bottom border for the same reason.

I've tried messing with the ordering of the conditions but nothing has worked. I feel like there should be a way to get the no-border cells to not affect the abutting rows that do have a top border, but I'm at a loss. Any ideas on how to solve this are appreciated!

Thanks,

--Robert

 

[Gerald Higgins]

Hi, welcome to the board.

It might be a good idea to post a small sample of your data, so that we can see exactly what you mean, and what you want to do.

You don't need to necessarily post data, maybe just a list will do, something like this . . .

Row 1 - this row should have a top single border, and a double bottom border
Row 2 - this row should have a double top border, and no bottom border

And so on.

Make sure you cover all the likely different scenarios.

 

[Musicman72]

Thanks Gerald - Here's a short example:


Excel 2013 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]hidden reference field[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]Row 1 - this one would have no borders because C2 is blank[/TD]
[TD]

111111​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]Row 2 - this row would also have no borders because C3 is "0"[/TD]
[TD]

222222​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]Row 3 - this row would have a top border because C4 is "1"[/TD]
[TD]

333333​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD]Row 4 - this row would default to single border top/double border bottom[/TD]
[TD]

444444​

[/TD]
[TD][/TD]
[TD](no condition on this row)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]


So, since conditional formatting can't be used to add double borders what I did is hard-formatted each cell to have a single border on top and a double border on the bottom. Then I'm trying to do the following conditions to clear or alter the borders:

1) If cell in column C is blank or zero - no borders
2) If cell in column C = 1 - single border on top, no bottom border
3) Otherwise leave the borders as is (so the single top/double bottom borders stay by default)

I'm feeding this data from another program, where the "C" column will get its values. Based on the result in the C column it would do the conditions above for the B column. The problem is that condition 1 above isn't working properly because it wipes out the top border on the next cell if there is supposed to be one.

Example: So in the sample above row B4 should have a single top border, but because I've cleared the borders in cell B3 using condition 1 it clears the bottom border for B3, which is the same as the top border on B4 which should be there. The same thing happens with the hard-coded double underlined cell that also has a single top border - if the cell above has its borders cleared conditionally then it also clears the top border for the cell below.

I'm guessing there is another way to "prioritize" which borders stay and go, but everything I've tried comes up with the same result, so I'm at a loss.

[TABLE="width: 970"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Sorry, how can each cell have a single border on top, and a double border on the bottom ?

Surely if a cell has a double border on the bottom, then the cell below it must have a double border on the top ?

 

[Musicman72]

I'm using a spreadsheet template which has add-on software that feeds data from our financial system into the template. Essentially the template only has one row of data, and when the program runs it populates an entire result set with formats that are dependent on the preset criteria. So, the template itself only has a single row of data. What I've done is taken that row and formatted it on the template so that there is a single border on top and a double border on the bottom. Then when the program runs it populates the result set including data in the "hidden" column, and based on that value I'm trying to condition out the borders on nearly all of the rows, or leave just a single top border on certain rows, based on my criteria in my earlier post. Normally I would just have the cells with no borders and use conditional formatting to add the single and double borders, but excel doesn't support adding double borders with conditional formatting. Make sense?

 

[Gerald Higgins]

OK, let's park the question of how this data gets into the spreadsheet, for a moment.

In post #3 . . . .
It says cell A3 has no borders, and cell A4 would have a top border.
How is this possible ?

I can see only two possible options for the shared border between A3 and A4 . . .

Option 1 - A3 has no bottom border, AND, A4 has no top border
Option 2 - A3 has a bottom border, AND A4 has a top border

 

[Musicman72]

That is the predicament - on most of the cells I don't want any borders top or bottom, but I don't know of a way of conditioning the "previous" cell to tell Excel that if the next row does have a top border then don't erase the bottom border on the previous cell, since it's the same as the top border on the next one. Unless there is a fancy IF statement that somehow reads the value of the following row's reference cell and if it's not zero or one then don't delete the bottom border on the previous cell, otherwise it'll delete the top border on the next one since they're essentially the same.

 

[Gerald Higgins]

Don't worry about HOW we do it just yet, let's just focus on WHAT YOU WANT IT TO LOOK LIKE.

I'm fairly confident there will be a method, but first we just need to establish what the results SHOULD BE.

Can you be really clear about that please ?

WHAT DO YOU WANT IT TO LOOK LIKE ?

I say again, don't get too hung up now on HOW we do it.