Results 1 to 12 of 12

Shorten the Code - Use CASE ?

Threaded View

  1. #1
    Registered User
    Join Date
    08-19-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    22

    Shorten the Code - Use CASE ?

    Hello

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

    Kind regards
    Vivek

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        If Not Intersect(Target(1), [D4:D400]) Is Nothing And (Target(1).Text) = "On Site" Then
            Application.EnableEvents = False
            Rows(Target(1).Row).Columns("E:AB").Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.499984740745262
                .PatternTintAndShade = 0
            End With
            Application.EnableEvents = True
        End If
        
        If Not Intersect(Target(1), [D4:D400]) Is Nothing And (Target(1).Text) = "Travel Air" Then
            Application.EnableEvents = False
            Rows(Target(1).Row).Columns("E:AB").Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.499984740745262
                .PatternTintAndShade = 0
            End With
            Application.EnableEvents = True
        End If
        
        If Not Intersect(Target(1), [D4:D400]) Is Nothing And (Target(1).Text) = "No Work" Then
            Application.EnableEvents = False
            Rows(Target(1).Row).Columns("E:AB").Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.499984740745262
                .PatternTintAndShade = 0
            End With
            Application.EnableEvents = True
        End If
        
        If Not Intersect(Target(1), [D4:D400]) Is Nothing And (Target(1).Text) = "Travel Road" Then
            Application.EnableEvents = False
            Rows(Target(1).Row).Columns("E:AB").Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            Application.EnableEvents = True
        End If
        
           If Not Intersect(Target(1), [D4:D400]) Is Nothing And (Target(1).Text) = "Virtual" Then
            Application.EnableEvents = False
            Rows(Target(1).Row).Columns("E:AB").Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            Application.EnableEvents = True
        End If
    
    End Sub
    Last edited by captvsharma; 03-25-2022 at 04:28 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Shorten VBA code
    By DiCaver in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2018, 05:40 AM
  2. shorten code vba
    By wildonln in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2016, 03:50 PM
  3. [SOLVED] Represent (define?) a section of code with a variable (shorten long code lines)?
    By Gene@action in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-01-2016, 03:59 PM
  4. Hi all, can you please help me to shorten this code as much as possible
    By boddulus in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-31-2014, 07:31 AM
  5. [SOLVED] Very inefficient code because of different sizes of arrays, how do i shorten my code?
    By Brammer88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-28-2012, 04:49 PM
  6. [SOLVED] Disregard case in VBA code. (UCase, LCase, Select Case)
    By Orestees in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-07-2012, 12:12 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1