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

0 votes

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
Jan 21 in Others by Kithuzzz
• 27,740 points
26 views

1 answer to this question.

0 votes

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
answered Jan 21 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 51,240 points
152 views
0 votes
1 answer
0 votes
1 answer

How do I get it to select a single row based on the value?

Try this: Sub MoveBasedOnValue2() Dim ...READ MORE

answered Jan 20 in Others by narikkadan
• 51,240 points
30 views
0 votes
1 answer

Excel VBA: Trying to read all files in folder based on cell input and output to another cell

Your array has no capacity for data ...READ MORE

answered Jan 24 in Others by narikkadan
• 51,240 points
37 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,382 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
274 views
0 votes
1 answer

VBA Help to find a column based on header value and cupy it to an other worksheet

You can break out the "copy column ...READ MORE

answered 5 days ago in Others by narikkadan
• 51,240 points
29 views
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 51,240 points
100 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP