在行中搜索单元格颜色，如果条件为true，则为给定范围着色

Question

我有一个代码，如果给定区域中的单元格有单词"Yes"，它们就会用红色突出显示。由于区域非常大，如果同一行中的任何单元格都用红色填充，我也希望用红色列a到I进行着色。在这里我留下我的代码。

Sub ChangeColor()
Set MR = Range("A2:CC127")
For Each cell In MR
If cell.Value = "Yes" Then
cell.Interior.ColorIndex = 3
ElseIf cell.Value = "No" Then
cell.Interior.ColorIndex = 15
End If
Next
End Sub

Answer 1

在为单元格着色时，只需添加一行即可为a中的相应单元格着色

Sub ChangeColor()
Set MR = Range("A2:CC127")
For Each cell In MR
If cell.Value = "Yes" Then
cell.Interior.ColorIndex = 3
cells(cell.row,1).Interior.ColorIndex = 3   ' NEW LINE HERE
ElseIf cell.Value = "No" Then
cell.Interior.ColorIndex = 15
End If
Next
End Sub

Answer 2

如您所述，以下代码还将输入范围的整列着色为浅红色(其他所有列着色为淡绿色(：

Const RNG As String = "B1:L6"
Sub ChangeColor()
Range(RNG).Interior.Color = RGB(191, 255, 191)
For Each col In Range(RNG).Columns
alreadycolored = False
For Each cel In col.Cells
If InStr(1, cel.Text, "yes", vbTextCompare) > 0 Then 'found
If Not alreadycolored Then
col.Interior.Color = RGB(255, 191, 191)
alreadycolored = True
End If
cel.Interior.Color = RGB(127, 0, 0)
End If
Next cel
Next col
End Sub

如果不清楚为什么/如何工作，请随时询问。

Answer 3

您只能处理相关单元格

Sub ChangeColor()
Dim f As Range
Dim firstAddress As String
With Range("A2:CC127") ' reference your range
Set f = .Find(what:="yes", lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False) ' try and find first cell whose content is "yes"
If Not f Is Nothing Then ' if  found
firstAddress = f.Address ' store first found cell address
Do
f.Interior.ColorIndex = 3 'color found cell 
Range("A:I").Rows(f.Row).Interior.ColorIndex = 3 ' color columns A to I cells of the same row of found cell    
Set f = .FindNext(f) ' try and find next "yes"
Loop While f.Address <> firstAddress ' stop at wrapping back to first found value
End If
End With
End Sub