Create a border around a range of dynamic cells

[riedyp]

Hello,
I have a table dependent on another table that users will input information. I am trying to make my table dynamic, meaning that I want the border to move up and down with the displayed values when it is updated by a command button.
For example, from my picture I would like the border to be around the range of B23 to H(newRow). I do not want a border inside of that dynamic range. Any suggestions on how one might do that? Thank you.

Private Sub UpdateButton_Click()
'newRow = ActiveSheet.Cells(1000, 3).End(xlUp).Row 'Goes to the last updated cell
'ActiveSheet.Cells(newRow, 3).Select
Dim lRow As Long, newRow As Long
lRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
For newRow = lRow To 1 Step -1
    If ActiveSheet.Cells(newRow, 3) <> 0 Then Exit For
Next
ActiveSheet.Cells(newRow, 3).Select
ActiveSheet.Range("B" & newRow).Resize(, 8).Borders(xlEdgeBottom).Weight = xlThin 'Creates bottom border
'ActiveSheet.Range(Cells(24,2),Cells(newRow,8).BorderAround , xlThin
'I want to create a border on the left,right and bottom of a range from B24 to H"newRow"
End Sub

 

[6StringJazzer]

I would use conditional formatting rule to put a bottom border on a cell if the current row is not blank and next row is blank.

 

[riedyp]

I would use conditional formatting rule to put a bottom border on a cell if the current row is not blank and next row is blank.

Thank you, how would I write this?

 

[6StringJazzer]

I wouldn't do it in the code. I would set up three conditional formatting rules on that sheet:

Applies to B24:I999 (change 999 to whatever is the maximum possible row you might use)
=AND($C24<>"",$C25="")
Format: Bottom border

Applies to B24:B999
=$C24<>""
Format: Left border

Applies to I24:I999
=$C24<>""
Format: Right border

 

[riedyp]

Thank you, I understand what you are saying now. I tried that but am having difficulties with it. I should note that the blank cells are actually filled with a formula
"='Project - Gantt Chart'!D23". But I did not think it would make a difference because the cells that they are referencing do not contain a value.

 

[6StringJazzer]

What is in cell 'Project - Gantt Chart'!D23 ? Is it empty, or does it also have a formula?

Is there any way for you to share the file?

 

[riedyp]

D23 is an empty cell. I wasn't sure if i am allowed to upload actual documents to the forum, but here are 2 screenshots of the "Project Gantt Chart" sheet.

 

[6StringJazzer]

I should note that the blank cells are actually filled with a formula
"='Project - Gantt Chart'!D23". But I did not think it would make a difference because the cells that they are referencing do not contain a value.

That formula should result in 0 if D23 is an empty cell. Are you using formatting to suppress the display of a 0 cell?

 

[riedyp]

Okay and yes. I am trying to create a border around only the values that will appear and when I add values the border will expand around those and any border within the new expanded border will disappear.

 

[6StringJazzer]

The image you showed in the first post is different than the subsequent one so I don't know which sheet is which or what you are really doing.

Here is an example of a sheet set up with the rules I described. I tried to make it look like your file although you didn't provide the formulas (the one formula you did provide--I'm not sure which direction it goes). Hopefully this will help you apply the same principle to your file.

Dropbox - File Deleted - Simplify your life

www.dropbox.com