VBA to AutoFit Row Height and AutoFit Column Width

[dslhs]

Hello,

I'm pretty sure there is a simple solution to this, but I'm struggling.

I would like a VBA code that is triggered by a change to the contents of the cell A2.

When A2 is changed, it will automatically AutoFit Row Height and AutoFit Column Width for the cells W7:AE42.

Private Sub Worksheet_Change(ByVal Target As Range)    
' Check if the changed cell is B2
If Target.Address = "$B$2" Then        
Application.EnableEvents = False                '
Range("W7:AE42").Rows.AutoFit        
Range("W7:AE42").Columns.AutoFit                
' Re-enable events        
Application.EnableEvents = True    
End IfEnd Sub

I thought this would work, but alas it hasn't.

Any help would be much appreciated.

 

[Alex Blakenburg]

I am about to log off, so if it is just a Data Validation drop down, try copying in the code below. It shouldn't make any difference.
If it still doesn't work then show us the picture I asked for in the previous post and advise on what type of drop down it is.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if the changed cell is A2
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Application.EnableEvents = False                '
        Range("W7:AE42").Rows.AutoFit
        Range("W7:AE42").Columns.AutoFit
        ' Re-enable events
        Application.EnableEvents = True
    End If
End Sub