Conditional Formatting to adjust column width

R

RichCowell

Board Regular
Joined
Dec 5, 2013
Messages
121
Office Version
  1. 365
Platform
  1. Windows
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:

Code: 
           E
1        dd
2   dd mmm yyyy
3       ddd

so a real example:

Code: 
           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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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?
 
Last edited:
Upvote 0
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.
NILNCuv.jpg
 
Upvote 0
Try

Code: 
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
 
Upvote 0
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
 
Last edited:
Upvote 0
Is there something in the cell?

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

Code: 
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
 
Upvote 0
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...

feA5R23.jpg
 
Upvote 0
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.

Code: 
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
 
Upvote 0
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:

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

I'll undo that and try yours though
 
Last edited:
Upvote 0
Yours works, and I think is the tidier option, so I'll use that, thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Conditional formatting based on value of another cell
Replies
3
Views
240
etaf
etaf
A
  • Question
Conditional Formatting Multiple Rows Based off 1st Column
2
Replies
10
Views
362
kevin9999
K
S
  • Question
VBA To Filter Conditionally Formatted coloured cells in multiple columns
Replies
1
Views
150
bobsan42
bobsan42
W
  • Solved
Copying Conditional Formatting with relative values
Replies
9
Views
543
weeksy
W
J
  • Solved
conditional format to change color
2
Replies
10
Views
210
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,215,003
Messages
6,122,655
Members
449,091
Latest member
peppernaut

Share this page

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