Highlight columns of weekends

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
382
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi All,
I use the code to highlight those weekend columns but it also highlights the blank cell column even though I added the code for the blank cell. Where went wrong, please ?

VBA Code:
Sub FindWeekendCol13()
  Dim rng As Range
    Dim lastRow As Long
    Dim rngCol As Range
    Dim lastCol As Long
   
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
   
    Set rng = Range("C1", Range("AL" & lastRow))
     
    For Each rngCol In rng.Columns
   
        rngCol.Cells.Interior.Color = xlNone
               
       'Debug.Print rngCol.Address
        If IsWeekend(rngCol.Cells(1)) = True Then
            ngCol.Cells.Interior.Color = RGB(255, 245, 230)
           
        ElseIf rngCol.Cells(1).Value = "" Then
            rngCol.Cells.Interior.Color = xlNone
           
        Else
            rngCol.Cells.Interior.Color = xlNone
             
        End If

    Next rngCol
   
 End Sub
 

Attachments

  • colorweekend col.png
    colorweekend col.png
    29.6 KB · Views: 8

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I'm not overly familiar with VBA.

What is IsWeekend() ?
That's not an internal VBA function is it?
If it's a UDF then you need to show that too.
 
Upvote 0
I assume you found it here

If so then IsWeekend() returns TRUE on a blank cell hence the unwanted highlighting.
Since the only time you want a cell highlighted is when it is a weekend
Try this

Code:
 'Debug.Print rngCol.Address
     rngCol.Cells.Interior.Color = xlNone
      If rngCol.Cells(1).Value <> "" and IsWeekend(rngCol.Cells(1)) = True Then
            ngCol.Cells.Interior.Color = RGB(255, 245, 230)
      End If
 
Upvote 0
Hi Special_K99,
My full code is here.
I modified the code as you suggested but not work !

VBA Code:
Public Function IsWeekend(InputDate As Date) As Boolean
    
        Select Case Weekday(InputDate)
        Case vbSaturday, vbSunday
            IsWeekend = True
        Case Else
            IsWeekend = False
    End Select
      
End Function


Sub FindWeekendCol13()
  Dim rng As Range
    Dim lastRow As Long
    Dim rngCol As Range
    Dim lastCol As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Set rng = Range("C1", Range("AL" & lastRow))
      
    For Each rngCol In rng.Columns
    
        rngCol.Cells.Interior.Color = xlNone
                
       'Debug.Print rngCol.Address
        rngCol.Cells.Interior.Color = xlNone
      If rngCol.Cells(1).Value <> "" And IsWeekend(rngCol.Cells(1)) = True Then
            rngCol.Cells.Interior.Color = RGB(255, 245, 230)
      Else
            rngCol.Cells.Interior.Color = xlNone
      End If
    Next rngCol
    
 End Sub
 
Upvote 0
Try this with few correction...
VBA Code:
Public Function IsWeekend(InputDate As Date) As Boolean
    
    Select Case Weekday(InputDate)
    Case vbSaturday, vbSunday
        IsWeekend = True
    Case Else
        IsWeekend = False
    End Select
      
End Function


Sub FindWeekendCol13()

    Dim rng As Range
    Dim lastRow As Long
    Dim rngCol As Range
    Dim lastCol As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Set rng = Range("C1", Range("AL" & lastRow))
    For Each rngCol In rng.Columns
        rngCol.Cells.Interior.Color = xlNone
        If rngCol.Cells(1).Value <> "" And _
            IsWeekend(rngCol.Cells(1)) = True Then
            rngCol.Columns.Interior.Color = RGB(255, 245, 230)
        Else
            rngCol.Cells.Interior.Color = xlNone
        End If
    Next rngCol
    
 End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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