Code improvement

[Beard]

Hello.

As I am learning, I was hoping someone could take a look at the below and see if there is a way to improve the code.

It works, I am just wondering if there is a better way of doing it.

Thank you.

For GRange = 0 To Sheet7.Cells(Rows.Count, "G").End(xlUp).Row

    If Sheet7.Range("G" & 6 + GRange).Text = "Y" Then
        
        Sheet7.Range("H" & 6 + GRange & ":" & "O" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("Q" & 6 + GRange & ":" & "R" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("R" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("T" & 6 + GRange & ":" & "U" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("U" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("W" & 6 + GRange & ":" & "X" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("X" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("Z" & 6 + GRange & ":" & "AA" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("AA" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("AC" & 6 + GRange & ":" & "AD" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("AD" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("AF" & 6 + GRange & ":" & "AG" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("AG" & 6 + GRange).Value = Sheet7.Range("B2").Value
        
    ElseIf Sheet7.Range("G" & 6 + GRange).Text = "X" Then
    
        Sheet7.Range("H" & 6 + GRange & ":" & "O" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("Q" & 6 + GRange & ":" & "R" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("T" & 6 + GRange & ":" & "U" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("W" & 6 + GRange & ":" & "X" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("Z" & 6 + GRange & ":" & "AA" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("AC" & 6 + GRange & ":" & "AD" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("AF" & 6 + GRange & ":" & "AG" & 6 + GRange).Interior.ColorIndex = 24
        
    End If


Next

 

[My Aswer Is This]

Some of the script is missing. It would be nice to see all of the script.

 

[Beard]

Some of the script is missing. It would be nice to see all of the script.

Of course, I didn't think it relevant.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False


Application.EnableEvents = False


For GRange = 0 To Sheet7.Cells(Rows.Count, "G").End(xlUp).Row


    If Sheet7.Range("G" & 6 + GRange).Text = "ŒÅ’è’l" Then
        
        Sheet7.Range("H" & 6 + GRange & ":" & "O" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("Q" & 6 + GRange & ":" & "R" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("R" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("T" & 6 + GRange & ":" & "U" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("U" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("W" & 6 + GRange & ":" & "X" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("X" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("Z" & 6 + GRange & ":" & "AA" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("AA" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("AC" & 6 + GRange & ":" & "AD" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("AD" & 6 + GRange).Value = Sheet7.Range("B2").Value
        Sheet7.Range("AF" & 6 + GRange & ":" & "AG" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("AG" & 6 + GRange).Value = Sheet7.Range("B2").Value
        
    ElseIf Sheet7.Range("G" & 6 + GRange).Text = "•Ï�”’l" Then
    
        Sheet7.Range("H" & 6 + GRange & ":" & "O" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("Q" & 6 + GRange & ":" & "R" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("T" & 6 + GRange & ":" & "U" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("W" & 6 + GRange & ":" & "X" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("Z" & 6 + GRange & ":" & "AA" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("AC" & 6 + GRange & ":" & "AD" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("AF" & 6 + GRange & ":" & "AG" & 6 + GRange).Interior.ColorIndex = 24
        
    End If


Next


Application.EnableEvents = True


Application.ScreenUpdating = True


End Sub

 

[My Aswer Is This]

I'm surprised when you say this script runs OK. Normally the statement Sheet7 would error out.
Sheet 7 normally has to be written as : Sheet(7) or Sheet("Sheet7")
Are you using an Apple Computer?

 

[GCExcel]

Hello,
Assuming the Sheet7 is the sheet's codename, I would simplify the code like this.
Note: Always good practice to define your variables)

Private Sub Worksheet_Change(ByVal Target As Range)    Dim GRange As Long
    Dim rg As Range, c As Range, j As Integer


    Application.ScreenUpdating = False
    Application.EnableEvents = False


    With Sheet7
    
        Set rg = .Range("G6:G" & .Cells(.Rows.Count, "G").End(xlUp).Row)
        
        For Each c In rg
            If c = "Y" Then
                c.Offset(0, 1).Resize(1, 8).Interior.ColorIndex = 19
                For j = 1 To 6
                    c.Offset(0, 10 + j * 3).Resize(1, 2).Interior.ColorIndex = 19
                    c.Offset(0, 11 + j * 3) = .Range("B2")
                Next j
            ElseIf c = "X" Then
                c.Offset(0, 1).Resize(1, 8).Interior.ColorIndex = 24
                For j = 1 To 6
                    c.Offset(0, 10 + j * 3).Resize(1, 2).Interior.ColorIndex = 24
                Next j
            End If
    
        Next c
    End With


    Application.EnableEvents = True
    Application.ScreenUpdating = True


End Sub

 

[Beard]

Hello,
Assuming the Sheet7 is the sheet's codename, I would simplify the code like this.
Note: Always good practice to define your variables)

Private Sub Worksheet_Change(ByVal Target As Range)    Dim GRange As Long
    Dim rg As Range, c As Range, j As Integer


    Application.ScreenUpdating = False
    Application.EnableEvents = False


    With Sheet7
    
        Set rg = .Range("G6:G" & .Cells(.Rows.Count, "G").End(xlUp).Row)
        
        For Each c In rg
            If c = "Y" Then
                c.Offset(0, 1).Resize(1, 8).Interior.ColorIndex = 19
                For j = 1 To 6
                    c.Offset(0, 10 + j * 3).Resize(1, 2).Interior.ColorIndex = 19
                    c.Offset(0, 11 + j * 3) = .Range("B2")
                Next j
            ElseIf c = "X" Then
                c.Offset(0, 1).Resize(1, 8).Interior.ColorIndex = 24
                For j = 1 To 6
                    c.Offset(0, 10 + j * 3).Resize(1, 2).Interior.ColorIndex = 24
                Next j
            End If
    
        Next c
    End With


    Application.EnableEvents = True
    Application.ScreenUpdating = True


End Sub

Thank you for the code changes.
I will study these changes and learn more, thank you.