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

 

[RichCowell]

Quick follow up - if I wanted to be able to specify the width in the spreadsheet rather than editing the code - is there any way to add a cell reference in the VBA rather than the number for the width?

 

[RichCowell]

Ignore - went back to Google, I'm going to set options for each day to share with a friend who'll be able to use it like that too.

Thanks again!

 

[RichCowell]

Okay got it sorted now - just in case you were interested to see what you've inspired/helped...

It now looks at cells for the widths, then afterwards if the 29th Feb is not a correct date - it sets that back to 0!

Perfect!

Thanks again!

Sub colwidth()
Dim lc As Long
lc = Cells(16, Columns.Count).End(xlToLeft).Column
For Each cell In Range(Cells(16, "E"), Cells(16, lc))
    
    If IsDate(cell) Then
        If Weekday(cell) = 1 Then
        
            Columns(cell.Column).ColumnWidth = Range("C10") 'change to what you want for Sundays
        
        End If
        If Weekday(cell) = 2 Then
        
            Columns(cell.Column).ColumnWidth = Range("C4") 'change to what you want for Mondays
        
        End If
        If Weekday(cell) = 3 Then
        
            Columns(cell.Column).ColumnWidth = Range("C5") 'change to what you want for Tuesdays
        
        End If
        If Weekday(cell) = 4 Then
        
            Columns(cell.Column).ColumnWidth = Range("C6") 'change to what you want for Wednesdays
        
        End If
        If Weekday(cell) = 5 Then
        
            Columns(cell.Column).ColumnWidth = Range("C7") 'change to what you want for Thursdays
        
        End If
        If Weekday(cell) = 6 Then
        
            Columns(cell.Column).ColumnWidth = Range("C8") 'change to what you want for Fridays
        
        End If
        If Weekday(cell) = 7 Then
        
            Columns(cell.Column).ColumnWidth = Range("C9") 'change to what you want for Saturdays
        
        End If
    End If
    If Not IsDate(cell) Then
    
            Columns(cell.Column).ColumnWidth = 0 'change to what you want for Feb 29's that aren't a leap year
    End If
    
Next cell
End Sub