VBA , To consolidate two worksheet_chang events or to add events to another one

ROUKWA

New Member
Joined
Feb 3, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.


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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

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