Colorize a line if a condition is true

Asked

Viewed 14 times

-1

Public Sub Worksheet_Change(ByVal Target As Excel.Range)

Set range1 = Range("B2:B20")


For Each cell In range1
    If cell = "Recente" Then
        With cell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
       
       
    ElseIf cell = "Retornado" Then
       With cell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
       End With
       
       
    ElseIf cell = "Antigo" Then
        
        With cell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
       End With
       
       
    ElseIf cell = "Caducado" Then
        With cell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
       End With
       
    ElseIf cell = "" Then
       With cell.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
       
       
    End If
    
 Next
 
End Sub

In my excel sheet I have a status column with the following options: "Returned", "Old", "Expired" and "Recent". The table as a whole possessed the following Range ("A1:L16"), the column with status has the following Range ("B1:B16"). The code consists of coloring, with certain coloring, the cells of the status club for each type of status present in it. I would like to color the line as a whole, and not just the cell.

Example:

Range Table ("A1:L16").

Range Status Column ("B1:B16").

Note: Table has header in range ("A1:L1").

When the cell ("B3") was changed to the "Returned" status, the Range color ("A3:L3") was changed to the color set within the WITH block that is in the IF statement of that status.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.