Shorten the Code - Use CASE ?

[captvsharma]

Hello

What would be a smarter way to shorten the below code please ?

Kind regards
Vivek

Please Login or Register  to view this content.

[AskMeAboutExcel]

Do you mean something like this (not tested)?

Please Login or Register  to view this content.

[Andy Pope]

A little shorter

Please Login or Register  to view this content.

Only check in D4:D400 once, then check for text.
The code is not case sensitive "on site" and "On Site" will both cause the change.

[AskMeAboutExcel]

good spot, hadn't picked up the different .TintAndShade parameters

[captvsharma]

Thanks and gives the idea

[captvsharma]

Andy , very clear and extremely succinct.

How can I build in multiple criteria in the same code ? e.g. If "ON SITE" is on Saturday or Sunday ( Date is Column C) than remove the TintandShade ?

[AskMeAboutExcel]

you could nest the case, or put simple IF...THEN statements on individual lines (depending on how complex they are)

[captvsharma]

Hello

Please help with building a nested CASE statement. If Column C (date) is a weekend i.e. Saturday or Sunday then the Tint for "On Site Work" should be removed. How can I achieve that in the code please ?
[CODE]
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target(1), [D4:D400]) Is Nothing Then
Select Case UCase(Target(1).Text)
Case "ON SITE WORK", "TRAVEL TO/FROM", "NO AUDIT WORK"
Application.EnableEvents = False
With Rows(Target(1).Row).Columns("E:AB").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
Application.EnableEvents = True
Case "TRAVEL WITHIN", "VIRTUAL WORK"
Application.EnableEvents = False
With Rows(Target(1).Row).Columns("E:AB").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.EnableEvents = True
Case Else
Application.EnableEvents = False
With Rows(Target(1).Row).Columns("E:AB").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.EnableEvents = True
End Select
End If

End Sub

[captvsharma]

Attaching the Excel sheet for understanding.

[captvsharma]

Thanks Sir. Please help with building a nested CASE statement. If Column C (date) is a weekend i.e. Saturday or Sunday then the Tint for "On Site Work" should be removed. How can I achieve that in the code, please ? Excel spreadsheet attached if it helps.

[Andy Pope]

add a check of weekday

Please Login or Register  to view this content.

In the weekday function I used 2 so the values of Saturday and Sunday were 6 and 7. This makes the conditional test easier.

[captvsharma]

Andy - wow ! What a great code and thanks a lot. You are awesome.