I appreciate you all in advance.
I realized that two or more worksheet_change events can't run in a single worksheet module when I finished writing code.
Once before, I asked in here and some kind guys helped me and I could finish writing code.
However, I have no idea what to do to consolidate or add below two codes into one.
Any advice or suggestions would be appreciate.
I realized that two or more worksheet_change events can't run in a single worksheet module when I finished writing code.
Once before, I asked in here and some kind guys helped me and I could finish writing code.
However, I have no idea what to do to consolidate or add below two codes into one.
Any advice or suggestions would be appreciate.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'https://www.mrexcel.com/board/threads/vba-separate-a-single-range-into-multiple-ranges.1229001/
Dim trlIntCol As Long, rPhIntCol As Long, adrIntCol As Long, iosIntCol As Long, cmnIntCol As Long
Dim rng As Range, cell As Range
Dim firstLvValFor As Variant
Dim secondLvValFor As Variant
Dim thirdLvValFor_01 As Variant
Dim thirLvValFor_02 As Variant
Dim lngCounter As Long
Dim lngArr As Long
Dim lngCol As Long
Dim varArr As Variant
Dim rngBig As Range
Const clngColWide As Long = 3
Const clngRowWide As Long = 3
'If Target.Count > 1 Then Exit Sub
trlIntCol = RGB(230, 37, 30)
rPhIntCol = RGB(255, 234, 0)
adrIntCol = RGB(126, 199, 216)
'adrIntCol = RGB(61, 220, 132)
iosIntCol = RGB(162, 170, 173)
cmnIntCol = RGB(165, 154, 202)
firstLvValFor = Array("TRIAL", "BASIC", "NOVICE", "INTERMEDIATE", "ADVANCED")
secondLvValFor = Array("OtherPhone", "Android", "iPhone")
thirdLvValFor_01 = Array("Beginner", "Text", "PhoneCall", "mail", "camera", "Browsing", "Apps", "Maps")
thirLvValFor_02 = Array("Security", "Wi-Fi", "SomeSnsApps_01", "SomeSnsApps_02")
varArr = Array("M", "Q", "U", "Y", "AC", "AG", "AK")
For lngCounter = 31 To 51 Step 4
For lngArr = LBound(varArr) To UBound(varArr)
If rngBig Is Nothing Then
Set rngBig = Cells(lngCounter, varArr(lngArr)).Resize(clngRowWide, clngColWide)
Else
Set rngBig = Union(rngBig, Cells(lngCounter, varArr(lngArr)).Resize(clngRowWide, clngColWide))
End If
Next lngArr
Next lngCounter
Set rng = Application.Intersect(Target, rngBig)
If Not rng Is Nothing Then
For Each cell In rng.Cells
If cell.Value = "TRIAL" And cell.Offset(0, -2).Value = "Session" Then
cell.Offset(0, -2).Resize(1, 3).Interior.Color = trlIntCol
cell.Offset(0, -2).Resize(1, 3).Font.Color = vbWhite
ElseIf cell.Value = "Session" And cell.Offset(0, 1).Value <> "" And _
cell.Offset(0, 2).Value = "TRIAL" Then
cell.Resize(1, 3).Interior.Color = trlIntCol
cell.Resize(1, 3).Font.Color = vbWhite
ElseIf IsError(Application.Match(cell.Value, secondLvValFor, 0)) = False And _
cell.Offset(0, -1).Value = "Session" And _
cell.Offset(0, 1).Value = "TRIAL" Then
cell.Offset(0, -1).Resize(1, 3).Interior.Color = trlIntCol
cell.Offset(0, -1).Resize(1, 3).Font.Color = vbWhite
ElseIf IsError(Application.Match(cell.Value, thirdLvValFor_01, 0)) = False And _
cell.Offset(0, -1).Value = "Android" And _
cell.Offset(0, -2).Value <> "Session" Then
cell.Offset(0, -2).Resize(1, 3).Interior.Color = adrIntCol
cell.Offset(0, -2).Resize(1, 3).Font.ColorIndex = xlColorIndexAutomatic
ElseIf cell.Value = "Android" And _
IsError(Application.Match(cell.Offset(0, 1).Value, thirdLvValFor_01, 0)) = False _
And cell.Offset(0, -1).Value <> "Session" Then
cell.Offset(0, -1).Resize(1, 3).Interior.Color = adrIntCol
cell.Offset(0, -1).Resize(1, 3).Font.ColorIndex = xlColorIndexAutomatic
Else
cell.Interior.ColorIndex = xlColorIndexNone
cell.Font.ColorIndex = xlColorIndexAutomatic
End If
Next cell
End If
end_here:
Set rng = Nothing
Set rngBig = Nothing
End Sub
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Dim rng As Range, cell As Range
Dim clsDayIntCol As Long
Dim lngCounter As Long
Dim lngArr As Long
Dim varArray As Variant
Dim rngBig As Range
Const cldgColWide As Long = 4
Const cldgrowWide As Long = 3
clsDayIntCol = RGB(166, 166, 166)
varArray = Array("N", "R", "V", "Z", "AD", "AH", "AL")
For lngCounter = 30 To 50 Step 4
For lngArr = LBound(varArray) To UBound(varArray)
If rngBig Is Nothing Then
Set rngBig = Cells(lngCounter, Columns(varArray(lngArr)).Column)
Else
Set rngBig = Union(rngBig, Cells(lngCounter, Columns(varArray(lngArr)).Column))
End If
Next lngArr
Next lngCounter
Set rng = Application.Intersect(Target, rngBig)
If Not rng Is Nothing Then
For Each cell In rng.Cells
If cell.Value = "Center Closed" Then
cell.Offset(1, -2).Resize(cldgrowWide, cldgColWide).Interior.Color = clsDayIntCol
ElseIf cell.Value = "Workshop Dayoff" Then
cell.Offset(1, -2).Resize(cldgrowWide, cldgColWide).Interior.Color = clsDayIntCol
Else
cell.Offset(1, -2).Resize(cldgrowWide, cldgColWide).Interior.ColorIndex = xlColorIndexNone
End If
Next cell
End If
end_here:
Set rng = Nothing
Set rngBig = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Number & " " & Err.Description
Resume end_here
End Sub