Change interior color of entire row based on column value VBA

Question

Need to insert a statement that when VDB (i,35) = Deleted that the entire row interior color turns color index 22 (coral).

I need this step to occur within this block, without needing to add code in a new block (unless absolutely necessary), as sheet has 20K plus entries. I am assuming since this is where I am identifying when the item has a status of "Deleted", and placing "Deleted" into column 35, I should be able to color it in this same step/block, and it would be most efficient method. I may be wrong..

Is there another line I can add after the last line, that will color these entries that ="Deleted" in col index 35?

I have tried passing vDB(i,35) to another variable as a range, and setting it, and then using if it = Deleted to change the entirerow.interior.color index = 22 , but I can't get the phrasing right, and may be taking wrong approach. I am still in learning curve but try to figure out my own issues, before bugging the group, but I can't seem to get it right.

Here is snip it.

'Execute Find (Vlookup)

For i = 1 To UBound(vDB, 1)
'If sht.Cells(i, 1).Value <> "" Then
    If vDB(i, 1) <> "" Then
        Set rng = rngData.Find(vDB(i, 1), LookIn:=xlValues, Lookat:=xlWhole) 'Matches entire contents of cell for an exact match
        If Not rng Is Nothing Then
           'If found return value of column 9 of ABC Recalc Cycle Count Remainder Browse (offset by 2), into ABC Matrix monthly ABC Code column, as determined by variable
            vDB(i, ABCCodeCell) = rng.Offset(, 7)
            'If found, return the value of column 7 of ABC Recalc Cycle Count Remainder Browse (offset by 2), into ABC Matrix column 27
            vDB(i, 27) = rng.Offset(, 5)
            'If found, return the value of column 11 of ABC Recalc Cycle Count Remainder Browse (offset by 2), into ABC Matrix column 34
            vDB(i, 33) = rng.Offset(, 9)
            'If found, place value of ABCMatrixMonthSelect.ComboBox1 in column AO Col Index 41
            vDB(i, 41) = ABCMatrixMonthSelect.ComboBox1.value
        Else
            vDB(i, 35) = "Deleted"
            vDB(i, 41) = ABCMatrixMonthSelect.ComboBox1.value
            With vDB(i, 1) = sht.Cells.Interior.Color = RGB(247, 119, 109) 'Light Red
            End With
        End If
    End If
    If vDB(i, ABCCodeCell) = vDB(i, lastMonthABCCode) Then
    vDB(i, 36) = "No"
    Else
    vDB(i, 36) = "Yes"
    End If
DoEvents
Next
rngDB = vDB

Dim LR As Long
LR = sht.Cells(Rows.Count, 1).End(xlUp).Row
sht.Cells.FormatConditions.Delete
With sht.Range("1:" & LR)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$AI1=""Deleted""" 'Searches for value "Deleted" in Range 1 to last row
    With .FormatConditions(.FormatConditions.Count)
       .SetFirstPriority
       With .Interior
           .Color = RGB(247, 119, 109) 'Light Red
       End With
    End With
End With

'Reset Excel Status Bar
Application.StatusBar = False
e here

Answer 1

If you can use conditional formatting, use the rule type: Use formula to determine which cells to format, in the formula bar type =$AJ35="Deleted", then define your range such as =1:1000. The Position of the $ in the formula is the key to that. You then would choose what happens when the criteria is met and set the rule.

You can do this from VBA, setting conditional formatting, such as:

Dim LR as Integer
LR = Cells(Rows.Count, 1).End(xlUp).Row 'just finding the row # of the last row
sht.Cells.FormatConditions.Delete
With sht.Range("1:" & LR) 'The range you're working with... specify the Rows() to ensure the whole row is colored 
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$AI1=""Deleted""" 'Where you put the cell you want to evaluate and criteria
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .Color = RGB(0, 70, 255) 'Arbitrary; you will need to find the specific color you want.
            .TintAndShade = 0.8
        End With
    End With
End With

Note that I remove existing conditional each time.

Additionally, another option is to just say, which doesn't use conditional formatting:

With sht.Rows(i).Interior
    .Color=RGB(0,255,0) 'arbitrary color
    .TintAndShade = 0.8
End With

---

Edit: With your adjusted code and desire for columns 1 to 35 to be color-coded:

Else
    vDB(i, 35).Value = "Deleted"
    With sht.Range(sht.Cells(i,1),sht.Cells(i,35)).Interior
        .Color = RGB(247, 167, 184) 'light red
        .TintAndShade = 0.8
    End With
End If

I'm thinking something is up with your vDB, so maybe it will be more appropriate to just use Cells?

Else
    Cells(i, 35).Value = "Deleted"
    With sht.Range(sht.Cells(i,1),sht.Cells(i,35)).Interior
        .Color = RGB(247, 167, 184) 'light red
        .TintAndShade = 0.8
    End With
End If

Similarly:

Dim i, LR as Integer
LR = Cells(Rows.Count,1).End(xlUp).Row 'Assumes row 1 is contiguous... probably not, given the rest of this code
For i = 1 To LR
    If Cells(i, 1) <> "" Then
        Set rng = rngData.Find(Cells(i, 1), LookIn:=xlValues, Lookat:=xlWhole)
        If Not rng Is Nothing Then
            Cells(i, ABCCodeCell).Value = rng.Offset(, 7).Value
            Cells(i, 27).Value = rng.Offset(, 5).Value
            Cells(i, 34).Value = rng.Offset(, 9).Value
        Else
            Cells(i, 35).Value = "Deleted"
            With sht.Range(sht.Cells(i,1),sht.Cells(i,35)).Interior
                .Color = RGB(247, 167, 184) 'light red
                .TintAndShade = 0.8
            End With