Conditional Formatting not working with borders

sparky2205

Well-known Member
Joined
Feb 6, 2013
Messages
507
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi folks,
this is a link to a spreadsheet: WeTransfer File.xlsm
It contains 2 worksheets.
"General Usage" is where the users enter their information.
"Print Data" is a copy of "General Usage" but with conditional formatting that only displays the relevant data. i.e. it doesn't display nonrelevant n/a. Some n/a are relevant and are displayed. If you look at "Print Data" you will see this. This is achieved by hiding data and borders as appropriate. All is well to this point.
When the user has entered all their data they then print the form using the "Print" button.
The macro behind the "Print" button collapses any nonrelevant rows in "Print Data"; ".EntireRow.Hidden = True". This works fine unless the last row is hidden. If the last row is hidden "Print Data" doesn't display the border on the last row of data when printed. It will show as present on "Print Data" but when the rows are collapsed by the macro the bottom border is not visible.
I think I know why this is happening. My CF formula removes the bottom border from the last row which, when collapsed, overrides the CF on the last row of the previous section. But that may not be correct.
I have tried several variations e.g. including "and the previous section <> n/a" in the CF formula but I still get the same result. That bottom border is missing every time.
Note: Running the macro will hide "Print Data" and protect the workbook but the password is an empty string.

Any help is greatly appreciated.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I'm not sure if this is helpful but when I print out a range of cells I always format the row after the last DATA row so its top border is what is desired. Include that "extra" row in the print range. That way the desired "bottom" borders are what I want.
 
Upvote 0
Thanks for the reply OaklandJim,
my issue is the collapsing of the cells. If I print out the sheet without collapsing the cells the bottom border is there. Or if the last section is populated the bottom border is also there.
It's when the cells are collapsed i.e. hidden, and the last section is not populated that the problem occurs.
e.g. if the second last section, "Verify and record" is the last populated section, the last section "Other" will have its borders hidden and the rows will be collapsed.
So row 34 is now collapsed beneath row 31. Bottom borders take precedence when overlapping but it's like the bottom border of row 34 is fighting for precedence over the bottom border of row 31 and row 34 is winning.
Setting the top border of row 35 won't work in this case because of the order of precedence when borders clash.
At least that's what I think is going on. I've tried several different formulas in CF but to no avail. That bottom border is always absent.
 
Upvote 0
Success!
I took your suggestion OaklandJim and brought it a step further. So thanks for the prod.
Into all bar the first collapsing IF, I added code to put a bottom border on the preceding row.
So, if I'm collapsing the last section, i.e. rows 32-34 then I explicitly put a bottom border on row 31 with vba.
This is what I was trying to achieve with CF but I just couldn't get it to work. This is much simpler anyway as the CF formulas can get quite involved.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top