vary column width conditionally

Mike__

Board Regular
Joined
Jan 19, 2010
Messages
81
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Right click on the worksheet tab and choose "View Code" and paste this code.
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
Does this work for you?
Code:
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
 
Upvote 0

Forum statistics

Threads
1,211,848
Messages
6,104,355
Members
447,902
Latest member
chriswebs23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top