VBA to set row height automatically where a cell value equals a certain value

[clothian]

Hi - I'm new to vba, but not new to excel. I'm after setting the row height automatically where a cell value equals a certain value.

I'm using a Named Range "Row_Type" which is located within a Table "Table1". The Named Range is located in Column C and this is the 2nd Column in "Table1".

Where the cell value within "Row_Type" is Empty or "Row Type 1", then set Row Height to 20
Where the cell value within "Row_Type" is "Row Type 2", then set Row Height to 15
Where the cell value within "Row_Type" is "Row Type 3" or "Row Type 4" or "Row Type 5" or "Row Type 6", then set Row Height to 10

I'm wanting this to be live / automatic too, so that as more rows are added to the range, or cells within the range change, row heights adjust as per the above rules. I thought I'd be able to modify some existing advice offered on here, but have failed.

This is what I've started with (might be miles off).....

Option Explicit

Sub Worksheet_Change(ByVal target As Range)
Dim MyRange As Range, MyVals As Variant, i As Long

    If Intersect(target, Range("Row_Type")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    MyVals = Range("Row_Type").Value
    For i = 4 To 126
        If (MyVals("Row_Type") = "Row Type 3" Or MyVals("Row_Type") = "Row Type 4" Or MyVals("Row_Type") = "Row Type 5" Or MyVals("Row_Type") = "Row Type 6") Then
            If MyRange Is Nothing Then
                Set MyRange = Range("Row_Type")
            Else
                Set MyRange = Union(MyRange, Range("Row_Type"))
            End If
        End If

    Next i
    MyRange.rowheight = 10
    Application.ScreenUpdating = True
    
End Sub

Any help would be much appreciated.

 

[Fluff]

How are the values being changed?

 

[clothian]

How are the values being changed?

User selecting the value (Row Type 1, Row Type 2 etc. etc.), from list (data validation drop down).

 

[Fluff]

Ok, how about

Sub Worksheet_Change(ByVal Target As Range)
   Dim MyRange As Range, MyVals As Variant, i As Long
   Dim Tbl As ListObject
   
   If Target.CountLarge > 1 Then Exit Sub
   Set Tbl = Me.ListObjects("Table1")
   If Not Intersect(Target, Tbl.ListColumns(2).DataBodyRange) Is Nothing Then
      Application.ScreenUpdating = False
      Select Case Target
         Case "", "Row Type 1"
            Target.RowHeight = 20
         Case "Row Type 2"
            Target.RowHeight = 15
         Case Else
            Target.RowHeight = 10
      End Select
   End If
End Sub

 

[clothian]

Ok, how about

Sub Worksheet_Change(ByVal Target As Range)
   Dim MyRange As Range, MyVals As Variant, i As Long
   Dim Tbl As ListObject
  
   If Target.CountLarge > 1 Then Exit Sub
   Set Tbl = Me.ListObjects("Table1")
   If Not Intersect(Target, Tbl.ListColumns(2).DataBodyRange) Is Nothing Then
      Application.ScreenUpdating = False
      Select Case Target
         Case "", "Row Type 1"
            Target.RowHeight = 20
         Case "Row Type 2"
            Target.RowHeight = 15
         Case Else
            Target.RowHeight = 10
      End Select
   End If
End Sub

The above didn't appear to do anything, so I tried adding in "Application.ScreenUpdating = True" as below, but still nothing:

Sub Worksheet_Change(ByVal Target As Range)
   Dim MyRange As Range, MyVals As Variant, i As Long
   Dim Tbl As ListObject
  
   If Target.CountLarge > 1 Then Exit Sub
   Set Tbl = Me.ListObjects("Table1")
   If Not Intersect(Target, Tbl.ListColumns(2).DataBodyRange) Is Nothing Then
      Application.ScreenUpdating = False
      Select Case Target
         Case "", "Row Type 1"
            Target.RowHeight = 20
         Case "Row Type 2"
            Target.RowHeight = 15
         Case Else
            Target.RowHeight = 10
      End Select
   End If
   
Application.ScreenUpdating = True

End Sub

The

Set Tbl = Me.ListObjects("Table1")

- Does this need the Table to be a named range called this, or is this just the Table Name (I have tried both ways)?

 

[Fluff]

It's the name of the table, did you get any errors?

 

[clothian]

It's the name of the table, did you get any errors?

No Errors - nothing happens. (tried entering info into the row types too)

 

[Fluff]

Ok, is the code in the correct sheet module?
Right click the tab of the sheet containing the table & select view code. Is the code I posted there?

 

[clothian]

Ok, is the code in the correct sheet module?
Right click the tab of the sheet containing the table & select view code. Is the code I posted there?

That's got it! - Great - thank you ?

 

[Fluff]

You're welcome & thanks for the feedback.