add another criteria

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
667
Office Version
  1. 365
Platform
  1. Windows
hi & thanks for your help,

I have use the code below , but i need to add in 'Bank Hol - Off' into , but after several failed attmpts I am cannot fathom how to?
Coul some in the know, kindly place into ?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Application.ScreenUpdating = False
    Dim rng As Range
       
        For Each rng In Range("e9:E39")
        If LCase(rng.Value) = "off" Then
            With Range("A" & rng.Row).Resize(1, 7)
                .Interior.ColorIndex = 36
                .Font.Bold = True
            End With
           
           
            
         ElseIf rng.Value = "" Then
            With Range("A" & rng.Row).Resize(1, 7)
                .Interior.ColorIndex = xlNone
                .Font.Bold = False
            End With
        End If
    Next rng

       
    Application.ScreenUpdating = True


If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub



    On Error Resume Next

    If Not Intersect(Target, Range("jb6:jb18")) Is Nothing Then

        Application.EnableEvents = False

        Target = UCase(Target)

        Application.EnableEvents = True

    End If
      

    On Error GoTo 0


If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub



    On Error Resume Next

    If Not Intersect(Target, Range("e6:e40")) Is Nothing Then

        Application.EnableEvents = False

        Target = StrConv(Target, vbProperCase)

        Application.EnableEvents = True

    End If

    On Error GoTo 0

      
    
  
End Sub

much appreciated.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You mean changing this line:
VBA Code:
        If LCase(rng.Value) = "off" Then
to this?
VBA Code:
        If (LCase(rng.Value) = "off") Or (LCase(rng.Value) = "bank hol - off") Then
 
Upvote 0
Solution
You mean changing this line:
VBA Code:
        If LCase(rng.Value) = "off" Then
to this?
VBA Code:
        If (LCase(rng.Value) = "off") Or (LCase(rng.Value) = "bank hol - off") Then
hi ,

thanks for your reply. No I need to keep 'off' & also add 'bank hol - off'. Sorry for any confusion.
 
Upvote 0
hi ,

thanks for your reply. No I need to keep 'off' & also add 'bank hol - off'. Sorry for any confusion.
That is exactly what that line does - notice I used "Or" (which means only one of those requirements need to be met) and not "And" (which requires BOTH requirements to be met).
Have you tried it?
 
Upvote 0
That is exactly what that line does - notice I used "Or" (which means only one of those requirements need to be met) and not "And" (which requires BOTH requirements to be met).
Have you tried it?
Hi Joe4,

sorry..works great, thank you.. Of course I be stuck if the user states ' oh, can I have 'bank hol - off ' in another colour !!!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top