Conditional formats and macros
 

I have created a vacation calendar so our admin can just enter dates
and the calendar is created with formulats and conditional formatting.

It is working well with formulas and conditional formatting. But now I
want to visually note half days and days staff will be remote using
other formatting options.

As such I have exceeded the conditional format limit. I've seen the
vba discussions about additional conditional formatting items and that
is a beginning for what I need to do.

My current scenario is covered with only 3 conditional formats except
for one item. Any row that is highlighting a weekend day should be
shaded. I would like to do this part in VB but am having trouble as I
am a novice in vb.

Some constraints of this scenario include:
- the date heading row is row 4 (not that absolute top)
- the date headings are dynamic - always based off the existing day
[today()]

Any guidance on this would be greatly appreciated.

Conditional formats and macros
 

"Don" wrote in message
oups.com...
I have created a vacation calendar so our admin can just enter dates
and the calendar is created with formulats and conditional formatting.

It is working well with formulas and conditional formatting. But now I
want to visually note half days and days staff will be remote using
other formatting options.

As such I have exceeded the conditional format limit. I've seen the
vba discussions about additional conditional formatting items and that
is a beginning for what I need to do.

My current scenario is covered with only 3 conditional formats except
for one item. Any row that is highlighting a weekend day should be
shaded. I would like to do this part in VB but am having trouble as I
am a novice in vb.

Some constraints of this scenario include:
- the date heading row is row 4 (not that absolute top)
- the date headings are dynamic - always based off the existing day
[today()]

Any guidance on this would be greatly appreciated.


If the workbook is opened daily, something along these lines in the code
page of "ThisWorkBook" should work. You'll have to add a line to make sure
you've got the correct sheet selected before the macro runs. You'll need to
tweak the fill colors to meet your preferences but this does what you want.

If the workbook stays open, you may need to run the code manually.

Hope this helps,

Walt

Public Sub ShadeWeekEnd()
Dim sFormatRange As String
Range("A5").Select
Do Until IsEmpty(ActiveCell)
sFormatRange = "A" & ActiveCell.Row & ":G" & ActiveCell.Row
If WorksheetFunction.Weekday(ActiveCell, 2) = 6 Or _
WorksheetFunction.Weekday(ActiveCell, 2) = 7 Then
Range(sFormatRange).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Else
Range(sFormatRange).Select
Selection.Interior.ColorIndex = xlNone
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Select

End Sub

Conditional formats and macros
 

Joe Bob wrote:
"Don" wrote in message
oups.com...
I have created a vacation calendar so our admin can just enter dates
and the calendar is created with formulats and conditional formatting.

It is working well with formulas and conditional formatting. But now I
want to visually note half days and days staff will be remote using
other formatting options.

As such I have exceeded the conditional format limit. I've seen the
vba discussions about additional conditional formatting items and that
is a beginning for what I need to do.

My current scenario is covered with only 3 conditional formats except
for one item. Any row that is highlighting a weekend day should be
shaded. I would like to do this part in VB but am having trouble as I
am a novice in vb.

Some constraints of this scenario include:
- the date heading row is row 4 (not that absolute top)
- the date headings are dynamic - always based off the existing day
[today()]

Any guidance on this would be greatly appreciated.


If the workbook is opened daily, something along these lines in the code
page of "ThisWorkBook" should work. You'll have to add a line to make sure
you've got the correct sheet selected before the macro runs. You'll need to
tweak the fill colors to meet your preferences but this does what you want.

If the workbook stays open, you may need to run the code manually.

Hope this helps,

Walt

Public Sub ShadeWeekEnd()
Dim sFormatRange As String
Range("A5").Select
Do Until IsEmpty(ActiveCell)
sFormatRange = "A" & ActiveCell.Row & ":G" & ActiveCell.Row
If WorksheetFunction.Weekday(ActiveCell, 2) = 6 Or _
WorksheetFunction.Weekday(ActiveCell, 2) = 7 Then
Range(sFormatRange).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Else
Range(sFormatRange).Select
Selection.Interior.ColorIndex = xlNone
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Select

End Sub

Thank you - am going to give it a whirl right now.