I cannot use conditional formatting because each of the three blocks already
has formulas in them. Plus I need to accomplish to actions with these code.

So I would like some assistance in developing the code to utilize the
Worksheet_Change event using VBA.

1. I am trying to apply a thick border to the left side of the column
running from row 5 through row 56 for any column when the value in row 3 of
that column equals 1.

Thanks Bill

2. The code would need to check row 3 starting with column C going right.
Example check C3, D3 E3€¦. When the code hits the last cell that does not
contain a value then I need to apply a thick border to the right side of the
column running from row 5 through row 56 for the ending row

This should get you started. If not you will need to make your explanation a
little clearer. E.G. do you want the last used column to have a right border
or is it the first empty column that has the border.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And _
Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub

Regards
Rowan

"Bill" wrote:

I cannot use conditional formatting because each of the three blocks already
has formulas in them. Plus I need to accomplish to actions with these code.

So I would like some assistance in developing the code to utilize the
Worksheet_Change event using VBA.

1. I am trying to apply a thick border to the left side of the column
running from row 5 through row 56 for any column when the value in row 3 of
that column equals 1.

Thanks Bill

2. The code would need to check row 3 starting with column C going right.
Example check C3, D3 E3€¦. When the code hits the last cell that does not
contain a value then I need to apply a thick border to the right side of the
column running from row 5 through row 56 for the ending row

I receive a type error with the code. If I put in in a blank worksheet with
no other code it tends to work with one small exception. The range of cells
in row three is updated automatically when the target address "a4" is
updated. Can this be fixed.

'from my sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$4" Then
Call DateFiller
Call WeekendFiller
Call BottomDataFiller
End If

Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub




"Rowan" wrote:

This should get you started. If not you will need to make your explanation a
little clearer. E.G. do you want the last used column to have a right border
or is it the first empty column that has the border.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And _
Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub

Regards
Rowan

"Bill" wrote:

I cannot use conditional formatting because each of the three blocks already
has formulas in them. Plus I need to accomplish to actions with these code.

So I would like some assistance in developing the code to utilize the
Worksheet_Change event using VBA.

1. I am trying to apply a thick border to the left side of the column
running from row 5 through row 56 for any column when the value in row 3 of
that column equals 1.

Thanks Bill

2. The code would need to check row 3 starting with column C going right.
Example check C3, D3 E3€¦. When the code hits the last cell that does not
contain a value then I need to apply a thick border to the right side of the
column running from row 5 through row 56 for the ending row

Hi Bill

I'm not sure where you are getting the error but if you want the borders
applied after A4 is updated then try it something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
Dim Counter As Integer
Application.EnableEvents = False
If Target.Address = "$A$4" Then
Call DateFiller
Call WeekendFiller
Call BottomDataFiller

endCol = Cells(3, Columns.Count).End(xlToLeft).Column
For Counter = 3 To endCol
If Cells(3, Counter).Value = 1 Then
With Range(Cells(5, Counter), Cells(56, Counter))
.Borders(xlEdgeLeft).Weight = xlThick
End With
End If
Next Counter
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
Application.EnableEvents = True
End Sub

Hope this helps
Rowan


"Bill" wrote:

I receive a type error with the code. If I put in in a blank worksheet with
no other code it tends to work with one small exception. The range of cells
in row three is updated automatically when the target address "a4" is
updated. Can this be fixed.

'from my sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$4" Then
Call DateFiller
Call WeekendFiller
Call BottomDataFiller
End If

Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub




"Rowan" wrote:

This should get you started. If not you will need to make your explanation a
little clearer. E.G. do you want the last used column to have a right border
or is it the first empty column that has the border.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And _
Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub

Regards
Rowan

"Bill" wrote:

I cannot use conditional formatting because each of the three blocks already
has formulas in them. Plus I need to accomplish to actions with these code.

So I would like some assistance in developing the code to utilize the
Worksheet_Change event using VBA.

1. I am trying to apply a thick border to the left side of the column
running from row 5 through row 56 for any column when the value in row 3 of
that column equals 1.

Thanks Bill

2. The code would need to check row 3 starting with column C going right.
Example check C3, D3 E3€¦. When the code hits the last cell that does not
contain a value then I need to apply a thick border to the right side of the
column running from row 5 through row 56 for the ending row

Rowan

Thanks. That worked perfect. Can you help me with one last thing? How can
I make I guess an array so that the below code will apply the border to rows
4, 10, 13, 19, 22, 24, 37.

With Range(Cells(58, 2), Cells(58, endCol))
..Borders(xlEdgeBottom).Weight = xlThick
End With

Thanks
Bill

"Rowan" wrote:

Hi Bill

I'm not sure where you are getting the error but if you want the borders
applied after A4 is updated then try it something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
Dim Counter As Integer
Application.EnableEvents = False
If Target.Address = "$A$4" Then
Call DateFiller
Call WeekendFiller
Call BottomDataFiller

endCol = Cells(3, Columns.Count).End(xlToLeft).Column
For Counter = 3 To endCol
If Cells(3, Counter).Value = 1 Then
With Range(Cells(5, Counter), Cells(56, Counter))
.Borders(xlEdgeLeft).Weight = xlThick
End With
End If
Next Counter
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
Application.EnableEvents = True
End Sub

Hope this helps
Rowan


"Bill" wrote:

I receive a type error with the code. If I put in in a blank worksheet with
no other code it tends to work with one small exception. The range of cells
in row three is updated automatically when the target address "a4" is
updated. Can this be fixed.

'from my sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$4" Then
Call DateFiller
Call WeekendFiller
Call BottomDataFiller
End If

Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub




"Rowan" wrote:

This should get you started. If not you will need to make your explanation a
little clearer. E.G. do you want the last used column to have a right border
or is it the first empty column that has the border.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And _
Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub

Regards
Rowan

"Bill" wrote:

I cannot use conditional formatting because each of the three blocks already
has formulas in them. Plus I need to accomplish to actions with these code.

So I would like some assistance in developing the code to utilize the
Worksheet_Change event using VBA.

1. I am trying to apply a thick border to the left side of the column
running from row 5 through row 56 for any column when the value in row 3 of
that column equals 1.

Thanks Bill

2. The code would need to check row 3 starting with column C going right.
Example check C3, D3 E3€¦. When the code hits the last cell that does not
contain a value then I need to apply a thick border to the right side of the
column running from row 5 through row 56 for the ending row

Rowan

I apologize. Please disregard my previous email and if you have time and
its possible help my change the code to this.

Thanks. That worked perfect. Can you help me with one last thing? How can
I make the below code so that it will apply the border to the top row of any
cell within the Range("A5:A55) that contains text. The border would run from
column 2 to the last column endcol.

endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(58, 2), Cells(58, endCol))
..Borders(xlEdgeTop).Weight = xlThick
End With


"Bill" wrote:

Rowan

Thanks. That worked perfect. Can you help me with one last thing? How can
I make I guess an array so that the below code will apply the border to rows
4, 10, 13, 19, 22, 24, 37.

With Range(Cells(58, 2), Cells(58, endCol))
.Borders(xlEdgeBottom).Weight = xlThick
End With

Thanks
Bill

"Rowan" wrote:

Hi Bill

I'm not sure where you are getting the error but if you want the borders
applied after A4 is updated then try it something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
Dim Counter As Integer
Application.EnableEvents = False
If Target.Address = "$A$4" Then
Call DateFiller
Call WeekendFiller
Call BottomDataFiller

endCol = Cells(3, Columns.Count).End(xlToLeft).Column
For Counter = 3 To endCol
If Cells(3, Counter).Value = 1 Then
With Range(Cells(5, Counter), Cells(56, Counter))
.Borders(xlEdgeLeft).Weight = xlThick
End With
End If
Next Counter
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
Application.EnableEvents = True
End Sub

Hope this helps
Rowan


"Bill" wrote:

I receive a type error with the code. If I put in in a blank worksheet with
no other code it tends to work with one small exception. The range of cells
in row three is updated automatically when the target address "a4" is
updated. Can this be fixed.

'from my sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$4" Then
Call DateFiller
Call WeekendFiller
Call BottomDataFiller
End If

Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub




"Rowan" wrote:

This should get you started. If not you will need to make your explanation a
little clearer. E.G. do you want the last used column to have a right border
or is it the first empty column that has the border.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And _
Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub

Regards
Rowan

"Bill" wrote:

I cannot use conditional formatting because each of the three blocks already
has formulas in them. Plus I need to accomplish to actions with these code.

So I would like some assistance in developing the code to utilize the
Worksheet_Change event using VBA.

1. I am trying to apply a thick border to the left side of the column
running from row 5 through row 56 for any column when the value in row 3 of
that column equals 1.

Thanks Bill

2. The code would need to check row 3 starting with column C going right.
Example check C3, D3 E3€¦. When the code hits the last cell that does not
contain a value then I need to apply a thick border to the right side of the
column running from row 5 through row 56 for the ending row

Hi Bill

Maybe like this - untested

dim Cell as Range
dim endCol as integer
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
for each cell in range("A5:A55")
if cell.value < empty then
With Range(Cells(cell.row, 2), Cells(cell.row, endCol))
..Borders(xlEdgeTop).Weight = xlThick
End With
end if
next cell

Regards
Rowan

"Bill" wrote:

Rowan

I apologize. Please disregard my previous email and if you have time and
its possible help my change the code to this.

Thanks. That worked perfect. Can you help me with one last thing? How can
I make the below code so that it will apply the border to the top row of any
cell within the Range("A5:A55) that contains text. The border would run from
column 2 to the last column endcol.

endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(58, 2), Cells(58, endCol))
.Borders(xlEdgeTop).Weight = xlThick
End With


"Bill" wrote:

Rowan

Thanks. That worked perfect. Can you help me with one last thing? How can
I make I guess an array so that the below code will apply the border to rows
4, 10, 13, 19, 22, 24, 37.

With Range(Cells(58, 2), Cells(58, endCol))
.Borders(xlEdgeBottom).Weight = xlThick
End With

Thanks
Bill

"Rowan" wrote:

Hi Bill

I'm not sure where you are getting the error but if you want the borders
applied after A4 is updated then try it something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
Dim Counter As Integer
Application.EnableEvents = False
If Target.Address = "$A$4" Then
Call DateFiller
Call WeekendFiller
Call BottomDataFiller

endCol = Cells(3, Columns.Count).End(xlToLeft).Column
For Counter = 3 To endCol
If Cells(3, Counter).Value = 1 Then
With Range(Cells(5, Counter), Cells(56, Counter))
.Borders(xlEdgeLeft).Weight = xlThick
End With
End If
Next Counter
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
Application.EnableEvents = True
End Sub

Hope this helps
Rowan


"Bill" wrote:

I receive a type error with the code. If I put in in a blank worksheet with
no other code it tends to work with one small exception. The range of cells
in row three is updated automatically when the target address "a4" is
updated. Can this be fixed.

'from my sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$4" Then
Call DateFiller
Call WeekendFiller
Call BottomDataFiller
End If

Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub




"Rowan" wrote:

This should get you started. If not you will need to make your explanation a
little clearer. E.G. do you want the last used column to have a right border
or is it the first empty column that has the border.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endCol As Integer
If Target.Row = 3 And Target.Column 2 And _
Target.Value = 1 Then
With Range(Cells(5, Target.Column), Cells(56, Target.Column))
.Borders(xlEdgeLeft).Weight = xlThick
End With
endCol = Cells(3, Columns.Count).End(xlToLeft).Column
With Range(Cells(5, endCol), Cells(56, endCol))
.Borders(xlEdgeRight).Weight = xlThick
End With
End If
End Sub

Regards
Rowan

"Bill" wrote:

I cannot use conditional formatting because each of the three blocks already
has formulas in them. Plus I need to accomplish to actions with these code.

So I would like some assistance in developing the code to utilize the
Worksheet_Change event using VBA.

1. I am trying to apply a thick border to the left side of the column
running from row 5 through row 56 for any column when the value in row 3 of
that column equals 1.

Thanks Bill

2. The code would need to check row 3 starting with column C going right.
Example check C3, D3 E3€¦. When the code hits the last cell that does not
contain a value then I need to apply a thick border to the right side of the
column running from row 5 through row 56 for the ending row