Autofit VBA question

[helpme20]

I am trying to write a VBA formula using the Workbook Sheetchange formula and I need some help please.

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Columns().AutoFit
End Sub

I only want it to work with one of my sheets. it is named ProductFinder and I only want it to work for the data in cells, $Q$6:$W$104 (BTW I have named that cell range "Overall" if that helps)



Can someone help me with this? Thank you!

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   Set Rng = Intersect(Target, Range("Q6:W104"))
   If Not Rng Is Nothing Then Rng.Columns.AutoFit
End Sub

This needs to go in the ProductFinder sheet module.

 

[helpme20]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   Set Rng = Intersect(Target, Range("Q6:W104"))
   If Not Rng Is Nothing Then Rng.Columns.AutoFit
End Sub

This needs to go in the ProductFinder sheet module.

How do I add it in the module that is just for Sheet 4 that is named ProductFinder?

 

[Fluff]

Just paste it into the code module for that sheet.

 

[helpme20]

How do I add it in the module that is just for Sheet 4 that is named ProductFinder?

I can double click on Sheet 4 in Microsoft Excel Objects and then add the code.

When I do that, it doesn't automatically do the autofit?

 

[Fluff]

It will, whenever you change (manually or by code) a cell in the range Q6:W104

 

[Joe4]

When exactly do you want the code to run?

When someone changes a value in that range?
When the workbook is opened?
When that sheet is activated?

 

[helpme20]

When exactly do you want the code to run?

When someone changes a value in that range?
When the workbook is opened?
When that sheet is activated?

I have data in a separate worksheet and I have a cell in the Product Finder sheet that when you enter anything in Cell T4, it returns specific data in Q thru W Columns.

It may return 1 row of data based on what you entered in T4, but it also could return 40 rows of data. I need whatever it returns to Autofit to the Column Width and Row Height.

 

[Joe4]

OK, then put this VBA code on this sheet where you are entering the value into T4:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("T4")) Is Nothing Then
        Sheets("ProductFinder").Range("Q6:W104").Columns.AutoFit
    End If
End Sub

 

[Fluff]

In that case use

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "T4" Then
      Range("Q6:W104").Columns.AutoFit
   End If
End Sub