Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Formatting using Case Statement (Excel 97)

    Hi There again

    I am back with another little challenge with my leave roster system. I have three conditional formats set and guess what..............the boss has come up with another idea and I have no more conditional formats left. They want to indicate when a person has sick leave with red shading. In the current version if I type a P in the cell it shades the cell yellow. Is it possible to shade the cell red if I put in an 's'. Is it possible to make it happen using a macro with a case statement somehow?

    Kerry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting using Case Statement (Excel 97)

    It might be faster, if instead of using the change event to act like a cond format to use the macro for the initial "shading" of weekends and holidays. This shading will not change as often and so would trigger less code running.

    You could run the "shading code" as desired and then add the Red background with "S" as the third conditional format

    <pre>Option Explicit
    Sub ShadeNonWorkDays()
    Dim rShade As Range
    Dim rHolidays As Range
    Dim rDates As Range
    Dim rCell As Range
    Dim iCol As Integer
    Dim iMatch As Integer
    Dim AWF As WorksheetFunction

    Set AWF = Application.WorksheetFunction
    Set rShade = Range("D5:AH80")
    Set rHolidays = Range("Holidays")
    Set rDates = Range("D2:AH2")
    For iCol = 1 To rShade.Columns.Count
    iMatch = 0
    On Error Resume Next
    iMatch = AWF.Match(rDates(iCol), rHolidays, 0)
    On Error GoTo 0

    If WeekDay(rDates(iCol), 2) > 5 Or _
    iMatch <> 0 Then
    rShade.Columns(iCol).Interior.ColorIndex = 40
    End If
    Next

    Set rCell = Nothing
    Set rShade = Nothing
    Set rHolidays = Nothing
    Set rDates = Nothing
    End Sub</pre>


    Steve

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Formatting using Case Statement (Excel 97)

    This is a great idea. Thankyou Steve. As usual I have been able to get it to almost work.

    I get an error when I run the macro. I would expext the user to set up the 13 months and then run the "non working days macro".

    If WeekDay(rDates(iCol), 2) > 5 Or _
    iMatch <> 0 Then

    I have attached what I have done.

    Kerry

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting using Case Statement (Excel 97)

    The worksheet is protected.
    You must either unprotect the sheet when the macro runs or when you open the workbook and protect the sheet with the Userinterfaceonly = true

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Formatting using Case Statement (Excel 97)

    Sorry to be a pain. Even when unprotected the code still does not run. I tried it by manually unprotecting the sheets and it didnt work.

    I have put in the protection line in the macro but not sure if I have it correct.

    Kerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting using Case Statement (Excel 97)

    The macro runs fine on my system, even without unprotecting/reprotecting the worksheet. I'm using Excel 2002, perhaps that causes the difference.

    You put the instruction to unprotect the worksheet inside the loop. That means the worksheet is unprotected 31 times; that is not necessary. You can put the line

    ActiveSheet.Unprotect

    immediately above the line For iCol = 1 ...

  7. #7
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Formatting using Case Statement (Excel 97)

    Firstly let me clarify - the shading in this macro is meant for Saturday and Sundays and holidays.

    When you said it worked. Did you run the Create sheets macro? I did that on both 2002 and 97 and on each occassion it shaded the same cells in each worksheet and they arent weekends or holidays. (try starting at 1/1/04)

    If I use the other macro on its own (shadenonworkingdays) I still get this

    If WeekDay(rDates(iCol), 2) > 5 Or _
    iMatch <> 0 Then

    I put the macro button on the set up sheet in case the user wanted to add or change a holiday date after setting up.

    Kerry

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting using Case Statement (Excel 97)

    The error occurs for months having fewer than 31 days because you set the ranges

    Set rShade = Range("D5:AH80")
    Set rDates = Range("D2:AH2")

    without taking the number of days in the month into account. These ranges always have 31 columns. Try this version:

    Sub ShadeNonWorkDays()
    Dim rShade As Range
    Dim rHolidays As Range
    Dim rDates As Range
    Dim rCell As Range
    Dim iCol As Integer
    Dim iMatch As Integer
    Dim AWF As WorksheetFunction

    Set AWF = Application.WorksheetFunction
    ActiveSheet.Unprotect
    ' determine column past last day in month
    ' and subtract 4 to get number of columns to process
    iCol = Range("AF2:AI2").Find("Location").Column - 4
    Set rShade = Range("D5").Resize(76, iCol)
    Set rHolidays = Range("Holidays")
    Set rDates = Range("D2").Resize(1, iCol)
    For iCol = 1 To rShade.Columns.Count
    iMatch = 0
    On Error Resume Next
    iMatch = AWF.Match(rDates(iCol), rHolidays, 0)
    On Error GoTo 0

    If Weekday(rDates(iCol), 2) > 5 Or _
    iMatch <> 0 Then
    rShade.Columns(iCol).Interior.ColorIndex = 40
    End If
    Next
    ActiveSheet.Protect userinterfaceonly:=True

    Set rCell = Nothing
    Set rShade = Nothing
    Set rHolidays = Nothing
    Set rDates = Nothing
    End Sub

  9. #9
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Formatting using Case Statement (Excel 97)

    Hi Hans

    I had a inkling that might have been the problem.

    I still get an error at this line

    iCol = Range("D5:AI5").Find("Location").Column - 1

    Steve reminded me that I must have the code run for each of the sheets.


    Kerry

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting using Case Statement (Excel 97)

    Location is in ROw 2 not row 5, try this:

    iCol = Range("D2:AI2").Find("Location").Column - 1

    Steve

  11. #11
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Formatting using Case Statement (Excel 97)

    Sorry Steve but although that line works it still falls over at the next line even if I change the reference to D2? I can't deny, I am still a big time learner. All the ideas, but I battle with "how". <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Col = Range("D2:AI2").Find("Location").Column - 1
    Set rShade = Range("D2").Resize(76, iCol)
    Set rHolidays = Range("Holidays")
    Set rDates = Range("D2").Resize(1, iCol)
    For iCol = 1 To rShade.Columns.Count
    iMatch = 0
    On Error Resume Next
    iMatch = AWF.Match(rDates(iCol), rHolidays, 0)
    On Error GoTo 0

    If WeekDay(rDates(iCol), 2) > 5 Or _
    iMatch <> 0 Then
    rShade.Columns(iCol).Interior.ColorIndex = 40
    End If
    Next
    ActiveSheet.Protect userinterfaceonly:=True

    Set rCell = Nothing
    Set rShade = Nothing
    Set rHolidays = Nothing
    Set rDates = Nothing
    End Sub

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting using Case Statement (Excel 97)

    Before you start "tweaking code" you should make sure you understand it, try this:

    iCol = Range("D2:AI2").Find("Location").Column - 4

    You need to subtract 4 since you find "location" and want the column before (1), but you start in D so you also have to eliminate the count of the first 3 (3+1 = 4)

    Hans' original code will also work (and it will be faster since it does not start until the "29th" row since all months have 28 days:
    iCol = Range("AF2:AI2").Find("Location").Column - 4

    Why did you change it?

    What problem do you have with this line:
    Set rShade = Range("D2").Resize(76, iCol)

    I got no error with this line.

    I got the error in the line:
    If WeekDay(rDates(iCol), 2) > 5 Or _
    iMatch <> 0 Then

    since "rDates(iCol)" for the "location column" has no "weekday". The whole idea of the line is to limit the range to the number of days only (ie stop just before the location column).

    You can call the shading routine while you create the sheets (in the loop) by just "calling it":
    datDate = DateSerial(Year(datDate), Month(datDate) + 1, 1) 'already there
    ShadeNonWorkDays 'new line
    oNewSheet.Protect userinterfaceonly:=True 'already there

    Steve

  13. #13
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Formatting using Case Statement (Excel 97)

    Steve, I take your point. I thought I did understand, but obviously not. I warned, I am still a learner.

    What I have found is that if I run the ShadeNonWorkingDays macro by calling it in the Create Sheets Macro it works like a charm.

    I still run into a problem though, when I subsequently run ShadeNonWorkingDays after creating sheets. It falls over at this line:-

    iCol = Range("AF2:AI2").Find("Location").Column - 4

    And I admit, I have no idea why.

    You may ask why I would want to run it again after setting up the sheets. The reason is the user may make a mistake with the holidays and need to make a change as I have done with the attached example. I have made 2nd Jan a holiday instead of 1st.

    Kerry
    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting using Case Statement (Excel 97)

    It seems to run fine for me.

    I will make a speculation: When you run it "afterwards", Is the "activesheet" one of the "Month Sheets"? You will have this problem if it is not.

    The code works on the "Activesheet" of the workbook. If there is not a cell in the activesheet, that has "location" in the range AF2:AI2 then you will get this error.

    If you want to call the routine after the fact, then you will to select each of the sheets you want to reformat and run the code (it only works on 1 sheet).

    You could write code to loop thru all the Month sheets, Something like this: (I assume the sheets are 4 - 17, that is that you have 3 sheets before the first 1 and then you have 13 monthsheets. If you ever change this setup the code would need to be modified.

    <pre>Sub RedoNonWorking()
    Dim x As Integer
    For x = 4 To 17
    Worksheets(x).Activate
    ShadeNonWorkDays
    Next
    End Sub</pre>


    The code will be a little slow since it actually selects each sheet. It could be changed (with a little work) to have the "ShadeNonWorkDays" take a parameter (the sheet object) and then work on that sheet, this would not require any selecting.

    Also the code only adds the shading, it does not remove any. If you removed a holiday from the list, it would not "unshade" that date. You can have the code first unshade all the range at the start of the code to fix this:

    <pre>Set rShade = Range("D5").Resize(76, iCol) 'Already there
    rShade.Interior.ColorIndex = xlNone 'New line to unshade
    Set rHolidays = Range("Holidays") 'already there</pre>


    Hope this helps,
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •