underline bottom row

[methody]

Hello there
I want the bottom row of my range of cells to have a border along the bottom

I have a range of cells - top left cell F6 - which is 5 columns wide but with a variable numbers of rows. The numbers of rows is determined by how many entries are made in C6 down.
F6 has a simple IF formulas - IF(C6="","",C6)
F7 has a simple IF formulas - IF(C7="","",C7)
So if there is only one entry in the C column there will only be one row in my range and the border is put under the cells F6- J6
So if there are 2 entries in the C column (C6,C7) there will only be one row in my range and the border is put under the cells F7- J7

Using current region is no good as it include those cells with formulas.

Any help appreciated

 

[Juan Pablo González]

You could use conditional format for that.

Select columns F:J

Go to conditional format. If you have 2007+, select "Use a formula to determine..." and enter the following formula

=(ROW($F1)>=6)*(LEN($C1)>0)*(LEN($C2)=0)

Click the "Format" button and put a bottom border. Click OK, and you're done.

 

[methody]

Apologies Juan
I have been using conditional formatting but have been having compatability issues and so want to use code instead.

 

[Juan Pablo González]

I've used that trick since Excel 2000, so you shouldn't have an issue with it. With code you'll have to clear borders before as well, so keep that in mind.

 

[methody]

Have you used it in Excel 2010?

 

[Juan Pablo González]

Just tested it on 2010 and it works fine as well.

 

[methody]

OK thank you
Its just that I have a workbook save in Excel 2003 with this sort of condiftional formattting but when another person uses it who has 2010 they have a problem when it comes to saving and the problem points to the condtional formatting.