| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case within IF-Else statement
I'm trying to edit the following code but when I put the Select Case
after Then instead of after Else, it gives me an error. Why would it work for one but not the other? I'm a VBA beginner. If rngTopLeft.Value = "" Then .Interior.ColorIndex = 15 Else 'color blue/beige (37 for weekend, 40 for weekday) Select Case Weekday(rngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case within IF-Else statement
Sub test()
With rngTopLeft.Offset(0, 1) If rngTopLeft.Value = "" Then 'color blue/beige (37 for weekend, 40 for weekday) Select Case Weekday(rngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select Else End If End With syntactically worked fine for me. Possibly you had some type of typo. However, since the test is if the cell is empty, it seems pointless to have a case statement looking for values in the cell. -- Regards, Tom Ogilvy "rwnelson" wrote: I'm trying to edit the following code but when I put the Select Case after Then instead of after Else, it gives me an error. Why would it work for one but not the other? I'm a VBA beginner. If rngTopLeft.Value = "" Then .Interior.ColorIndex = 15 Else 'color blue/beige (37 for weekend, 40 for weekday) Select Case Weekday(rngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case within IF-Else statement
You're missing the With statements.
With RngTopLeft If .Value = "" Then .Interior.ColorIndex = 15 Else 'color blue/beige (37 for weekend, 40 for weekday) Select Case Weekday(RngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select End If End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "rwnelson" wrote in message ups.com... I'm trying to edit the following code but when I put the Select Case after Then instead of after Else, it gives me an error. Why would it work for one but not the other? I'm a VBA beginner. If rngTopLeft.Value = "" Then .Interior.ColorIndex = 15 Else 'color blue/beige (37 for weekend, 40 for weekday) Select Case Weekday(rngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case within IF-Else statement
Sorry, I posted the wrong code. When I run this code as is, the SELECT
CASE line gives me an error. '----------------------------------------------------------------------- Sub doRange(rngTopLeft As Range) With rngTopLeft.Resize(7, 2) If rngTopLeft.Value < Date Then .Interior.Pattern = xlGray50 Else .Interior.Pattern = xlSolid End If If rngTopLeft.Value = 1 And rngTopLeft.Interior.ColorIndex = 15 Then Select Case Weekday(rngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select End If If rngTopLeft.Value = "" Then .Interior.ColorIndex = 15 End If End With End Sub '------------------------------------------------------------------------- |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case within IF-Else statement
The code works for me. What exact error are you getting?
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "rwnelson" wrote in message oups.com... Sorry, I posted the wrong code. When I run this code as is, the SELECT CASE line gives me an error. '----------------------------------------------------------------------- Sub doRange(rngTopLeft As Range) With rngTopLeft.Resize(7, 2) If rngTopLeft.Value < Date Then .Interior.Pattern = xlGray50 Else .Interior.Pattern = xlSolid End If If rngTopLeft.Value = 1 And rngTopLeft.Interior.ColorIndex = 15 Then Select Case Weekday(rngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select End If If rngTopLeft.Value = "" Then .Interior.ColorIndex = 15 End If End With End Sub '------------------------------------------------------------------------- |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case within IF-Else statement
Sorry, I posted the wrong code. When I run this code as is, the SELECT
CASE line gives me an error. '----------------------------------------------------------------------- Sub doRange(rngTopLeft As Range) With rngTopLeft.Resize(7, 2) If rngTopLeft.Value < Date Then .Interior.Pattern = xlGray50 Else .Interior.Pattern = xlSolid End If If rngTopLeft.Value = 1 And rngTopLeft.Interior.ColorIndex = 15 Then Select Case Weekday(rngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select End If If rngTopLeft.Value = "" Then .Interior.ColorIndex = 15 End If End With End Sub '------------------------------------------------------------------------- |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case within IF-Else statement
You code works for me. EXACTLY what error do you get?
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "rwnelson" wrote in message oups.com... Sorry, I posted the wrong code. When I run this code as is, the SELECT CASE line gives me an error. '----------------------------------------------------------------------- Sub doRange(rngTopLeft As Range) With rngTopLeft.Resize(7, 2) If rngTopLeft.Value < Date Then .Interior.Pattern = xlGray50 Else .Interior.Pattern = xlSolid End If If rngTopLeft.Value = 1 And rngTopLeft.Interior.ColorIndex = 15 Then Select Case Weekday(rngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select End If If rngTopLeft.Value = "" Then .Interior.ColorIndex = 15 End If End With End Sub '------------------------------------------------------------------------- |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case within IF-Else statement
Sorry for the double postings, there were some server problems earlier
and I didn't think they went through. When I ran it again, it came up as type mismatch and highlighted the SELECT CASE line. However, when I removed the formatting from the screen and tried again, it worked just fine. Anyway, I got around this issue with the following code: '---------------------------------------------------------------------- With rngTopLeft.Resize(7, 2) If rngTopLeft.Value < Date Then .Interior.Pattern = xlGray50 Else .Interior.Pattern = xlSolid End If If rngTopLeft.Interior.ColorIndex = 15 And rngTopLeft.Value = 1 Then If rngTopLeft.Column = Range("F5").Column Or _ rngTopLeft.Column = Range("R5").Column Then .Interior.ColorIndex = 37 Else .Interior.ColorIndex = 40 End If End If If rngTopLeft.Value = "" Then .Interior.ColorIndex = 15 End If End With End SUb '------------------------------------------------------------------- Thanks for your help with this one. Now I remember why programming gave me headaches in high school. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case within IF-Else statement
Sorry, I posted the wrong code. When I run this code, the SELECT CASE
line gives me an error. '------------------------------------------------- Sub colors() Dim iRow As Long Dim iCol As Long For iRow = Range("F7").Row To Range("R42").Row Step 7 For iCol = Range("F7").Column To Range("R42").Column Step 2 doRange Cells(iRow, iCol) Next iCol Next iRow End Sub Sub doRange(rngTopLeft As Range) With rngTopLeft.Resize(7, 2) If rngTopLeft.Value < Date Then .Interior.Pattern = xlGray50 Else .Interior.Pattern = xlSolid End If If rngTopLeft.Value = 1 And rngTopLeft.Interior.ColorIndex = 15 Then Select Case Weekday(rngTopLeft.Value) Case 1, 7 'Sunday or saturday .Interior.ColorIndex = 37 Case Else .Interior.ColorIndex = 40 End Select End If If rngTopLeft.Value = "" Then .Interior.ColorIndex = 15 End If End With End sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select Case Statement | Excel Worksheet Functions | |||
| select case statement | Excel Programming | |||
| Select Case Statement does not evaluate | Excel Programming | |||
| Convert If..Else to Select Case Statement. | Excel Programming | |||
| Data validation with the Select Case statement | Excel Programming | |||
Linear Mode
