If a cell contains 'x' then select row to left and clear formatting through loop

[BillyB]

Hi,

This is my first ever post - scary stuff!

I've been hard at learning VBA for both excel and word for the past year to help me create massive efficiencies in my day to day duties at work.

Currently, I'm working on an excel sheet that is a snapshot of projects that are currently being run.

In column T, when the project is done, the user places an "x" to denote that the project is finished and complete.

The rest of the sheet has several conditional formatting rules that present an array of colour.

I'm attempting to write a code that when an "x" is placed in T2 or T3 or T4 etc, A2:S2 is selected, conditional formatting is cleared and then the row is highlighted as a single darker grey colour.

So far I've been able to get this to work for one instance. This is where I get stuck. I *THINK* the loop breaks down after the first instance where an "x" is not located? This is my code.

Any help is appreciated thanks!

Sub DiscolourRow()

Dim ws As Worksheet
Dim lRow As Long
Set ws = ThisWorkbook.Sheets("JobBoard")


With ws
    lRow = .Range("T" & .Rows.Count).End(xlUp).Row
    For i = 2 To lRow
        If .Cells(i, 1) = "x" Then
        
            .Range("A" & i).Select
    Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("B" & i).Select
    Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("C" & i).Select
    Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("D" & i).Select
               Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("E" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("F" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("G" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("H" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("I" & i).Select
               Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("J" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("K" & i).Select
               Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("L" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("M" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("N" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("O" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("P" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("Q" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("R" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("S" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            .Range("T" & i).Select
                Selection.FormatConditions.Delete
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
            
   
        End If
    Next i
End With


End Sub

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window


When you enter a x in column T the script will automatically run and do what you wanted.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/25/2019  4:07:27 AM  EDT
If Target.Column = 20 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "x" Then
    With Target.Offset(, -19).Resize(, 20)
        .FormatConditions.Delete
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.ThemeColor = xlThemeColorDark1
        .Interior.TintAndShade = -0.499984740745262
        .Interior.PatternTintAndShade = 0
    End With
End If
End If
End Sub

 

[BillyB]

Thank you. You solved my problem a different way and I believe it works much more efficiently.

Cheers,
Billy

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank you. You solved my problem a different way and I believe it works much more efficiently.

Cheers,
Billy