Q:

Draw table according to user provided width and height

I am very new with VBA in Excel. What I want to accomplish is this. When a user enters a length of say 5, then 5 columns must be outlined red. Then also when a user enters a width of say 6, then 6 rows must be outlined red. Example:

I have this code thus far:
On worksheet change:
Private Sub Worksheet_Change(ByVal Target As Range)
  If (Target.Address = "$A$2") Then
    Call Draw2DTankl
  ElseIf (Target.Address = "$B$2") Then
    Call Draw2DTankw
  End If
End Sub

Draw2DTankl:
Sub Draw2DTankl()

  On Error Resume Next
  Cells(2, 4).Value = ""
  Dim x As Range
  Set x = Worksheets("Sheet1").Cells

  x.Borders.LineStyle = xNone

  Range("A1") = "Length"

  Dim Length As Integer

   Length = CInt(Cells(2, 1).Value)

   If (Length > 30) Then
    MsgBox "A length of a maximum 30 is allowed"
    Exit Sub
  End If
  If (Length < 0) Then
    MsgBox "Invalid length value entered"
    Exit Sub
  End If

   Dim Rws As Long, Rng As Range, r As Range
    If (Length > 0) Then
    Rws = 20
    Set Rng = Range(Cells(20, "H"), Cells(Rws, 8 + Length - 1))

    For Each r In Rng.Cells

            With r.Borders

                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = 3

            End With
    Next r
    End If

If (Err.Number <> 0) Then
    MsgBox Err.Description
End If

End Sub

Draw2DTankw:
Sub Draw2DTankw()

  On Error Resume Next
  Cells(2, 4).Value = ""
  Dim x As Range
  Set x = Worksheets("Sheet1").Cells

  x.Borders.LineStyle = xNone

  Range("B1") = "Width"

  Dim Width As Integer

   Width = CInt(Cells(2, 2).Value)

  If (Width > 30) Then
    MsgBox "A width of a maximum 30 is allowed"
    Exit Sub
  End If
  If (Width < 0) Then
    MsgBox "Invalid Width value entered"
    Exit Sub
  End If

     Dim Col As Long, Rng As Range, r As Range
    If (Width > 0) Then
    Col = 21
    Set Rng = Range(Cells(21, "H"), Cells(Col, 8 + Length - 1))

    For Each r In Rng.Cells

            With r.Borders

                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = 3

            End With
    Next r
    End If

If (Err.Number <> 0) Then
    MsgBox Err.Description
End If

End Sub

Please help me. My code doesn't work. The length works, but that brakes when I change the width.
Entering my length draws:

Which is correct. But then if I enter the width of 6 this happens: (my length also dissapears)

I apologize for this long post!

A:

It looks like in the Draw2DTankw you have Width declared above but in the rng you are using length

Dim Width As Integer  Width = CInt(Cells(2, 2).Value)
Set Rng = Range(Cells(21, "H"), Cells(Col, 8 + Length - 1))

I've modified your code to draw both height and width by extending the range to include the width.  This worked with I test it.
Private Sub Worksheet_Change(ByVal Target As Range)
  If (Target.Address = "$A$2") Or (Target.Address = "$B$2") Then
  DrawTable
  End If
End Sub

Sub DrawTable()

  On Error Resume Next
  Cells(2, 4).Value = ""
  Dim x As Range
  Set x = ActiveSheet.Cells

  x.Borders.LineStyle = xNone

  Range("A1") = "Length"

  Dim Length As Integer
   Length = CInt(Cells(2, 1).Value)
   'Combined Width sections
  Dim Width As Integer
   Width = CInt(Cells(2, 2).Value)

   If (Length > 30) Then
    MsgBox "A length of a maximum 30 is allowed"
    Exit Sub
    ElseIf (Width > 30) Then
    MsgBox "A width of a maximum 30 is allowed"
    Exit Sub
    ElseIf (Length < 0) Then
    MsgBox "Invalid length value entered"
    Exit Sub
    ElseIf (Width < 0) Then
    MsgBox "Invalid Width value entered"
    Exit Sub
  End If

   Dim Rws As Long, Rng As Range, r As Range
    If (Length > 0) Then
    Rws = 20
    'Added width to cells(rws)
    Set Rng = Range(Cells(20, "H"), Cells(Rws + Width - 1, 8 + Length - 1))

    For Each r In Rng.Cells

            With r.Borders

                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = 3

            End With
    Next r
    End If

If (Err.Number <> 0) Then
    MsgBox Err.Description
End If
End Sub

