CODEWITHSUNDEEP
vbaexcel
Brapapple
Brapapple

Reputation: 3

VBA Excel Cell formatting on specific days on open

I would like to have a cell range (a9:e13) font colour changed to black if the day is a Monday, and changed to white if it is any other day. I would like this to execute as the file is opened.

So far i have 

Private Sub Workbook_open()
If Cell(S2).Value = True Then
Range("a9:e13").Font.Color = vbWhite
Else
Range("a9:e13").Font.Color = vbBlack
End If
End Sub

In cell S1 I have= =Today()

In cell S2 i have= =Weekday(S1) = 2

This is not working at all, can someone please help me understand my mistake?

The working Code

Private Sub Workbook_open()
If Sheet1.Cells(2, "S") = True Then
Sheet1.Range("a9:e13").Font.Color = vbBlack
Else
Sheet1.Range("a9:e13").Font.Color = vbWhite
End If
End Sub

Upvotes: 0

Views: 67

Answers (3)

IAmDranged
IAmDranged

Reputation: 3020

Another alternative is

Private Sub Workbook_open()
    With Worksheets("Your_sheet_name")
        If Weekday(Now) = vbMonday Then
            .Range("a9:e13").Font.Color = vbWhite
            Else
            .Range("a9:e13").Font.Color = vbBlack
        End If
    End with
End Sub

Edit: @ hnk - true, thanks for bringing this up. My thinking was that since this was part of the original requirements that any changes occur on workbook opening, and assuming an understanding of the implications involved, I just thought it would be fair to assume the scenario you point out would be unlikely to happen. But I acutally may have been better emphasizing that

Upvotes: 0

Robert Co
Robert Co

Reputation: 1715

I would do conditional formatting with this formula:

=WEEKDAY(TODAY())=2

Upvotes: 0

Herman
Herman

Reputation: 2998

I think the easiest solution is not using VBA, but using conditional formatting. For example, see http://office.microsoft.com/en-us/excel-help/use-a-formula-to-apply-conditional-formatting-HA102809768.aspx

In addition it seems like your code example has some errors, it should look like this? Where Sheet1 must be replaced by name of your sheet.

If Sheet1.Cells(2, "S") = True Then
    Sheet1.Range("a9:e13").Font.Color = vbWhite
Else
    Sheet1.Range("a9:e13").Font.Color = vbBlack
End If

Upvotes: 1

Related Questions