Conditional Formatting to adjust column width

[RichCowell]

Hi all!

I've got a calendar in a spreadsheet, horizontal with a column for each day.

the cells above each day are as follows:

           E
1        dd
2   dd mmm yyyy
3       ddd

so a real example:

           E
1        13
2   13 Apr 2018
3        Fri

I've got conditional formatting to fill the weekend days black (based on values beginning with "S" in E3)...

Is there any way with conditional formatting or VBS to automatically adjust the column width for Saturdays and Sundays so I can make them narrower?

It's all set to update based on the year, so it's not something I'd want to do manually if I can help it.

Thanks,

Rick

 

[Scott T]

You would need to use VBA as CF can not adjust the column width. Do you have actual dates in the cells or text? Can you give an example of your data layout how many columns do you need to possible resize? Do your date start in column A or B?

 

[RichCowell]

Thanks - I'd guessed it would be VBA (typo earlier!)...

The cells below what I put before will be mainly blank - I've added a screenshot below of an example month (blank as I'm just setting it up) - there will be probably 104 columns to resize depending on how the days fall in any year.
The cells with the days only have the days in - Excel's calculating that based on the date in the cell above.

 

[Scott T]

Try

Sub colwidth()
Dim lc As Long
lc = Cells(3, Columns.Count).End(xlToLeft).Column
For Each cell In Range(Cells(3, "E"), Cells(3, lc))
    
    If Weekday(cell) = 1 Or Weekday(cell) = 7 Then
    
        Columns(cell.Column).ColumnWidth = 7 [COLOR=#008000]'change to what you want the column width to be[/COLOR]
    
    End If
Next cell
End Sub

 

[RichCowell]

That's awesome! It looks like it's going to be perfect!

Is there any way of it ignoring cells that don't contain a day?

Only asking as I've hidden the column for February 29th so I don't have to add it manually when it's needed - so it's failing when it gets to that - but has worked on the others before it...

Thanks!

Edit: Feb 29th is (and will always be) Column GD btw

 

[Scott T]

Is there something in the cell?

This will not resize the column if row 3 in that column is blank.

Sub colwidth()
Dim lc As Long
lc = Cells(3, Columns.Count).End(xlToLeft).Column
For Each cell In Range(Cells(3, "E"), Cells(3, lc))
    
    If (Weekday(cell) = 1 Or Weekday(cell) = 7) And cell <> "" Then
    
        Columns(cell.Column).ColumnWidth = 7 'change to what you want
    
    End If
Next cell
End Sub

 

[RichCowell]

Yes - it's showing 29 Feb 2019 as all the dates are calculated on a formula - so that won't be detected as any day of the week...

 

[Scott T]

This will test if the value is a date sine 2/29/19 is not a valid date it should skip that column until there is a date.

Sub colwidth()
Dim lc As Long
lc = Cells(3, Columns.Count).End(xlToLeft).Column
For Each cell In Range(Cells(3, "E"), Cells(3, lc))
    
    If IsDate(cell) Then
        If Weekday(cell) = 1 Or Weekday(cell) = 7 Then
        
            Columns(cell.Column).ColumnWidth = 7 'change to what you want
        
        End If
    End If
Next cell
End Sub

 

[RichCowell]

Thanks - I'd thought of an alternative too - I've edited the formula in that cell to see if the day below begins with 29, display nothing, otherwise display the date:

=IF(LEFT(GD3,2)="29"," ",GD2&" "&LEFT(Month6, 3)&" "&$C$1)

I'll undo that and try yours though

 

[RichCowell]

Yours works, and I think is the tidier option, so I'll use that, thank you so much!