I appreciate you all in advance.
I want to change the meta range of below code (meta range:M31:AM53) to 42 separated ranges below.
Range("M31:O33,Q31:S33,U31:W33,Y31:AA33,AC31:AE33,AG31:AI33,AK31:AM33,M35:O37,Q35:S37,U35:W37,Y35:AA37,AC35:AE37,AG35:AI37,AK35:AM37,M39:O41,Q39:S41,U39:W41,Y39:AA41,AC39:AE41,AG39:AI41,AK39:AM41,M43:O45,Q43:S45,U43:W45,Y43:AA45,AC43:AE45,AG43:AI45,AK43:AM45,M47:O49,Q47:S49,U47:W49,Y47:AA49,AC47:AE49,AG47:AI49,AK47:AM49,M51:O53,Q51:S53,U51:W53,Y51:AA53,AC51:AE53,AG51:AI53,AK51:AM53")
When I convert the original meta range to that 42 ranges , VBA says "Syntax Error".
I googled and tried addition union methods(? what should i say it?) to the range.
It didn't meet any success, then syntax error happened.
I want to separate a single range into multiple non adjacent ranges.
How Can I handle this?
As the volunteer in charge of scheduling a smartphone workshop for seniors,
I have revised the calendar style schedule to accommodate multiple classes.
To streamline the process, I created a dependent drop-down menu within the calendar.
Each day now has 9 cells to represent the 3 periods of the workshop day.
To simplify inputting the classes, I divided the class names into 3 levels using a dependent drop-down menu,
for example, "Beginner," "Android," "Camera."
I anticipate that using conditional formatting may be challenging, due to huge petterns it should be.
Thus I want to achieve this with VBA.
Any advice and suggestions would be appreciated.
I want to change the meta range of below code (meta range:M31:AM53) to 42 separated ranges below.
Range("M31:O33,Q31:S33,U31:W33,Y31:AA33,AC31:AE33,AG31:AI33,AK31:AM33,M35:O37,Q35:S37,U35:W37,Y35:AA37,AC35:AE37,AG35:AI37,AK35:AM37,M39:O41,Q39:S41,U39:W41,Y39:AA41,AC39:AE41,AG39:AI41,AK39:AM41,M43:O45,Q43:S45,U43:W45,Y43:AA45,AC43:AE45,AG43:AI45,AK43:AM45,M47:O49,Q47:S49,U47:W49,Y47:AA49,AC47:AE49,AG47:AI49,AK47:AM49,M51:O53,Q51:S53,U51:W53,Y51:AA53,AC51:AE53,AG51:AI53,AK51:AM53")
When I convert the original meta range to that 42 ranges , VBA says "Syntax Error".
I googled and tried addition union methods(? what should i say it?) to the range.
It didn't meet any success, then syntax error happened.
I want to separate a single range into multiple non adjacent ranges.
How Can I handle this?
As the volunteer in charge of scheduling a smartphone workshop for seniors,
I have revised the calendar style schedule to accommodate multiple classes.
To streamline the process, I created a dependent drop-down menu within the calendar.
Each day now has 9 cells to represent the 3 periods of the workshop day.
To simplify inputting the classes, I divided the class names into 3 levels using a dependent drop-down menu,
for example, "Beginner," "Android," "Camera."
I anticipate that using conditional formatting may be challenging, due to huge petterns it should be.
Thus I want to achieve this with VBA.
Any advice and suggestions would be appreciated.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Dim trlRed As Long, oPhoneYellow As Long, adrGreen As Long, iosGrey As Long, cmnPurple As Long
Dim rng As Range, cell As Range
trlRed = RGB(230, 37, 30)
oPhoneYellow = RGB(255, 234, 0)
adrGreen = RGB(61, 220, 132)
iosGrey = RGB(162, 170, 173)
cmnPurple = RGB(165, 154, 202)
'firstLvValFor = Array("TRIAL", "BEGINNER", "NOVICE", "INTERMEDIATE", "ADVANCED")
secondLvValFor = Array("otherPhone", "Android", "iPhone", "Common")
thirdLvValFor_01 = Array("Basic", "Text", "PhoneCall", "mail", "camera", "Browsing", "Apps", "Maps")
thirLvValFor_02 = Array("Security", "Wi-Fi", "SomeSnsApps_01", "SomeSnsApps_02")
Set rng = Application.Intersect(Target, Me.Range("M31:AM53"))
If Not rng Is Nothing Then
For Each cell In rng.Cells
If cell.Value = "Session" And cell.Offset(0, -2).Value = "TRIAL" Then
cell.Offset(0, -2).Resize(1, 3).Interior.Color = trlRed
ElseIf IsError(Application.Match(cell.Value, thirdLvValFor_01, 0)) = False And cell.Offset(0, -1).Value = "otherPhone" And cell.Offset(0, -2).Value <> "TRIAL" Then
cell.Offset(0, -2).Resize(1, 3).Interior.Color = oPhoneYellow
ElseIf cell.Value = "otherPhone" And IsError(Application.Match(cell.Offset(0, 1).Value, thirdLvValFor_01, 0)) = False And cell.Offset(0, -1).Value <> "TRIAL" Then
cell.Offset(0, -1).Resize(1, 3).Interior.Color = oPhoneYellow
ElseIf IsError(Application.Match(cell.Value, thirdLvValFor_01, 0)) = False And cell.Offset(0, -1).Value = "Android" And cell.Offset(0, -2).Value <> "TRIAL" Then
cell.Offset(0, -2).Resize(1, 3).Interior.Color = adrGreen
ElseIf cell.Value = "Android" And IsError(Application.Match(cell.Offset(0, 1).Value, thirdLvValFor_01, 0)) = False And cell.Offset(0, -1).Value <> "TRIAL" Then
cell.Offset(0, -1).Resize(1, 3).Interior.Color = adrGreen
ElseIf IsError(Application.Match(cell.Value, thirdLvValFor_01, 0)) = False And cell.Offset(0, -1).Value = "iPhone" And cell.Offset(0, -2).Value <> "TRIAL" Then
cell.Offset(0, -2).Resize(1, 3).Interior.Color = iosGrey
ElseIf cell.Value = "iPhone" And IsError(Application.Match(cell.Offset(0, 1).Value, thirdLvValFor_01, 0)) = False And cell.Offset(0, -1).Value <> "TRIAL" Then
cell.Offset(0, -1).Resize(1, 3).Interior.Color = iosGrey
ElseIf IsError(Application.Match(cell.Value, thirLvValFor_02, 0)) = False And cell.Offset(0, -1).Value = "Common" And cell.Offset(0, -2).Value <> "TRIAL" Then
cell.Offset(0, -2).Resize(1, 3).Interior.Color = cmnPurple
ElseIf cell.Value = "Common" And IsError(Application.Match(cell.Offset(0, 1).Value, thirLvValFor_02, 0)) = False And cell.Offset(0, -1).Value <> "TRIAL" Then
cell.Offset(0, -1).Resize(1, 3).Interior.Color = cmnPurple
Else
cell.Interior.ColorIndex = xlColorIndexNone
End If
Next cell
End If
End Sub