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, 2023 in Others by Kithuzzz
• 38,010 points
394 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, 2023 by narikkadan
• 63,420 points

Related Questions In Others

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
• 63,420 points
996 views
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
• 63,420 points
1,028 views
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
1,462 views
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, 2023 in Others by narikkadan
• 63,420 points
319 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
830 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
3,127 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
• 23,260 points
423 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,670 points
689 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 Jan 26, 2023 in Others by narikkadan
• 63,420 points
1,446 views
0 votes
1 answer

VBA Excel: Draw line between cells based on cell value

In accordance with your description and with ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
819 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