vary column width conditionally

[Mike__]

Here's the crude psuedo code which executes anytime a value in column2 changes.

If row1 column2 meets condition then
column1 width = 10
else column1 width = 5

 

[taurean]

Right click on the worksheet tab and choose "View Code" and paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then 'Column 2 and row 1 ---> B1
    If Target.Value = 2 Then 'Write your condition here!
        Columns("B:B").ColumnWidth = 10
    Else
        Columns("B:B").ColumnWidth = 5
    End If
End If
End Sub

 

[Mike__]

Can the "target.address" be referenced as column B without any specific cell?

 

[taurean]

Can the "target.address" be referenced as column B without any specific cell?

Sure:

If Target.Column = 2 Then 'Column B

 

[Mike__]

I have it working using unique cells but I need it to work in a more general fashion. I'm not able to integrate "Target.Column" where it works at all. This doesn't work but you can at least see what I'm trying to do.

Private Sub Worksheet_Change(ByVal Target As Range)
For n = 9 To 67
If Target.Address = ("$C$" & n) Then
    If Target.Value < Range("$C$" & n-1).Value Then
        Columns("B:B").ColumnWidth = 20
    Else
        Columns("B:B").ColumnWidth = 5
    End If
End If
Next n
End Sub

 

[taurean]

Does this work for you?

Private Sub Worksheet_Change(ByVal Target As Range)
'==================================================================================
'If we are checking for a specific column then we don't need to check whole column
'Change Range C9:C67 to suit your requirement!
'==================================================================================
If Not Intersect(Target, Range("C9:C67")) Is Nothing Then
    If Target.Value < Target.Offset(-1, 0).Value Then
        Columns("B:B").ColumnWidth = 20
    Else
        Columns("B:B").ColumnWidth = 5
    End If
End If
End Sub

 

[Mike__]

It works perfectly! Thanks.