根据列值VBA更改整行的内部颜色

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

需要插入一个声明，当VDB（i，35）=删除时，整行内部颜色变为颜色索引22（珊瑚色）。

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..

我需要在此块中执行此步骤，而无需在新块中添加代码（除非绝对必要），因为工作表具有20K以上的条目。我假设，因为这是我在识别项目状态为“已删除”并将“已删除”放入第35列时所在的位置，我应该能够在同一步骤/块中对其进行着色，这将是最有效的方法。我可能错了..

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

我可以在最后一行之后添加另一行，它会在col索引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.

我已经尝试将vDB（i，35）传递给另一个变量作为范围，然后设置它，然后使用if if = Deleted来改变整个内部.interior.color index = 22，但我无法正确使用，并可能采取错误的方法。我还在学习曲线，但在开始讨论这个小组之前，我试图找出自己的问题，但我似乎无法做到正确。

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

1 个解决方案

#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.

如果可以使用条件格式，请使用规则类型：使用公式确定要格式化的单元格，在公式栏中键入= $ AJ35 =“已删除”，然后定义范围，例如= 1：1000。 $在公式中的位置是关键。然后，您将选择满足条件时发生的情况并设置规则。

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

您可以从VBA执行此操作，设置条件格式，例如：

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:

编辑：使用您调整的代码并希望对第1列到第35列进行颜色编码：

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?

我在想你的vDB有什么用，所以也许使用Cell更合适？

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

#1