Formatting using Case Statement (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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

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

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 …

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

iCol = Range(“D2:AI2”).Find(“Location”).Column – 1

Steve

Reply | Quote

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”.

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

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

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:

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

Hope this helps,
Steve

Reply | Quote

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.

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

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:

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

Hope this helps,
Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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”.

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

Reply | Quote

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

iCol = Range(“D2:AI2”).Find(“Location”).Column – 1

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 …

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

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

Steve

Reply | Quote