Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formats, how to scroll and view all formats? | Excel Worksheet Functions | |||
Conditional formats- paste special formats? | Excel Discussion (Misc queries) | |||
paste conditional formats as formats | Excel Discussion (Misc queries) | |||
Formats & Macros | Excel Discussion (Misc queries) | |||
CONDITIONAL FORMATS | Excel Worksheet Functions |