Trevor3007
Well-known Member
- Joined
- Jan 26, 2017
- Messages
- 667
- Office Version
- 365
- Platform
- 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 ?
much appreciated.
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.