VBA Excel -在包含基于另一个单元格值的值的行中着色eacht单元格

I am currently working on a macro that does a few different things and I am stuck now on the last part of the process.

我现在正在做一个宏观的工作，做一些不同的事情，我现在被困在这个过程的最后一部分。

I have a table and one column beside that table which indicates the color the values of the table should be in. To make it clear what I mean I am attaching this picture:

我在该表旁边有一个表和一列，它指示该表的值应该是什么颜色。为了说明我的意思，我附上这张照片:

Now, as you can see, there are entries in column O which indicate in which color the entries in B:N should be. For example: O3's value is r. This means that C3:E3 should be colored red. Here is how it should look like after the macro has done it's work:

现在，正如你所看到的，在O列中有一些条目表明B:N中的条目应该用什么颜色。例如:O3的值是r，这意味着C3:E3应该是红色的。这是在宏完成它的工作后它应该是什么样子的:

As you can see it is crucial that the macro only colors those cells in the row which actually have a value/any value. Empty cells should not be colored.

正如您所看到的，重要的是宏只对行中具有值/任何值的单元格进行着色。空单元格不应该被着色。

Any help in this regard would be very much appreciated!

在这方面的任何帮助都将非常感谢!

Thanks in advance.

提前谢谢。

1 个解决方案

#1

You may try something like this...

你可以试试这个……

Sub ApplyInteriorColor()
Dim rng As Range
Dim lr As Long, i As Long
Dim clr As Long
clr = vbRed
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("B3:N" & lr).Interior.ColorIndex = xlNone
If lr > 2 Then
    For i = 3 To lr
        Set rng = Range(Cells(i, 2), Cells(i, "N"))
        If Application.CountA(rng) > 0 And Cells(i, "O") <> "" Then
            Select Case Cells(i, "O").Value
                Case "r'"
                    clr = vbRed
                Case "b"
                    clr = vbBlue
                Case "y"
                    clr = vbYellow
            End Select
            rng.SpecialCells(xlCellTypeConstants, 3).Interior.Color = clr
        End If
    Next i
End If
End Sub