VBA Change Cell colors based on value and it can deal with single cell and multiple cells changes

Question

I want to thank you in advance. I work as a volunteer at a session for elders on smartphones.

I'm in charge of making a schedule for the pupils in the form of a calendar.

But because we have so many classes, I created a Sunday start calendar form and added the classes as dependent dropdowns to it to make the timetable easier. e.g. Horizontally, there are three levels: "Beginner," "Android Smartphone," and "Camera."

As a result, I must modify the colours of several cells based on certain values in a single cell.

Users may also enter numerous values onto sheets, such as the entire day, 1, 2, 3, or the same classes.

And users have the option to instantly delete the values for those classes if they change their minds, in which case 3 by 3 cells can be eliminated.

I tested the following code , but VBA shows me error code 13.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim trlRed As Long
    Dim adrBlue As Long
    
    trlRed = RGB(230, 37, 30)
    adrBlue = RGB(126, 199, 216)
    
    If Not Intersect(Target, Range("M31:AM53")) Is Nothing Then
        For Each cell In Target.Cells
         If cell.Value = "Session" And cell.Offset(0, -2).Value = "Trial" Then
                cell.Offset(0, -2).Resize(1, 3).Interior.Color = trlRed 

         ElseIf cell.Value = "AndroidSmartphone" And cell.Offset(0, -1).Value <> "trial" Then
                cell.Offset(0, -1).Resize(1, 3).Interior.Color = adrBlue
            Else
                cell.Resize(1, 3).Interior.ColorIndex = xlColorIndexNone
            End If
        Next cell
    End If
End Sub

Answer 1

Before looping through all of the cells in Target, your code checks to see whether any of the cells in Target are also in M31:AM53. Only the Target cells that fall under the interest range should be looped over.

You might attempt to access (eg) cell if you loop through every Target cell.

For a cell in Column A or B, use Offset(0, -2).

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim trlRed As Long, adrBlue As Long, rng As Range, cell As Range
    
    trlRed = RGB(230, 37, 30)
    adrBlue = RGB(126, 199, 216)
    
    Set rng = Application.Intersect(Target, Me.Range("M31:AM53"))
    If Not rng Is Nothing Then 'only loop though any cells in M31:AM53
        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 cell.Value = "AndroidSmartphone" And cell.Offset(0, -1).Value <> "trial" Then
                cell.Offset(0, -1).Resize(1, 3).Interior.Color = adrBlue
            Else
                cell.Resize(1, 3).Interior.ColorIndex = xlColorIndexNone
            End If
        Next cell
    End If
End Sub