CODEWITHSUNDEEP
excelvba
BlissC
BlissC

Reputation: 851

Add border to cells in a table if another row is added in excel

I have an Excel spreadsheet which spans columns A to T and for ease of reading, the cells have borders. Currently when another row of data is added to the spreadsheet though, I, or other users, have to add borders around the cells, just for cells in columns A to T where is something (usually, but not always, a date) in Column B on that row. The spreadsheet is a log of survey results, and gets very long by the end of the year, but doesn't have a defined number of rows, because it's being added to all the time. It's not much of a problem for me to add borders, but other users not familiar with Excel either manage to add borders to all the cells in columns A to T making the end of the list look untidy with cells with borders but no contents or they just don't bother adding borders and it gets very messy for managers to read (apparently!).

I'm trying to use conditional formatting with a formula to format the cells, but can't seem to get the formula right. I'm basically trying to get Excel to add a border around cells in columns A to T on any row where the cell in column B on that row isn't empty. I've tried various formulas with no luck so far and just end up with borders everywhere but where I want them!

Some of what I've tried so far (I can't remember all the ones that didn't work!): (row 4 is the first row of data)

=$B2<>"" (applied to =$A:$T) (adds borders above the table, but not below)

NOT(ISBLANK($B4)) (applied to =$A:$T) (doesn't add any additional borders)

=B4<>"" (applied to =$A:$T) (doesn't add any additional borders)

=not(exact(B4, "")) (applied to =$A:$T) (doesn't add any additional borders)

...and of course using Excel's inbuilt "cell doesn't contain a blank value" doesn't allow you to specify that you want to know if another cell isn't blank. It'll add borders around cells after you've input into them, but if a cell in another column is blank for any reason, it leaves 'holes' in the table where there aren't any borders which makes it look even more messy.

Am I just fundamentally misunderstanding the formulas, or can this not be done? (or do I need VBA to do this?)

Upvotes: 1

Views: 1196

Answers (1)

Scott Craner
Scott Craner

Reputation: 152475

Your formula needs to refer to the first row of the Applied To area:

=$B1<>""

And apply it to =$A:$T

Upvotes: 1

Related Questions