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!

 

[Joe4]

in Sheet4 the Product Finder sheet, when you enter anything in cell T4, it returns the information from Sheet3 to columns Q thru W. each row has formulas in cells Q6 thru W104

q6 =IFERROR(VLOOKUP(ROWS($Q$6:Q6),$H$6:$O$104,2,0),"")

r6 =IFERROR(VLOOKUP(ROWS($Q$6:U6),$H$6:$O$104,8,0),"")
...

That is very curious, as those formulas do not appear to be looking at cell T4 at all. So, I don't see how entering/changing the value in cell T4 would have any impact on any of those formulas.
Unless, you are using Conditional Formatting on those cells that looks at whether or not T4 has a value, and if it doesn't, it hides them (by making the text appear white).
Can you explain the relationship between T4 and those cells?

 

[helpme20]

If anyone else has any suggestions.

I can send the spreadsheet to any of you to look at if you're willing.

 

[helpme20]

That is very curious, as those formulas do not appear to be looking at cell T4 at all. So, I don't see how entering/changing the value in cell T4 would have any impact on any of those formulas.
Unless, you are using Conditional Formatting on those cells that looks at whether or not T4 has a value, and if it doesn't, it hides them (by making the text appear white).
Can you explain the relationship between T4 and those cells?

T4 is tied into a search in column F6-f104

=IF(ISNUMBER(SEARCH($T$4,@INDIRECT($B6))),MAX(F$5:$F5)+1,0)

 

[Fluff]

Have you saved the workbook as macro enabled?

 

[helpme20]

yes, I have save it as macro enabled sheet .xlsm file.

I need it to autofit both the column width and row height

 

[Fluff]

Do you have wrap text turned on for those columns?

 

[helpme20]

Do you have wrap text turned on for those columns?

yes I do

 

[Fluff]

In that case you will have to remove it, if you want to autofit the columns & rows
Try

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

 

[helpme20]

In that case you will have to remove it, if you want to autofit the columns & rows
Try

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

not sure why it doesn't work.

 

[Fluff]

If you use this

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

do you get the message box appear?