vba 如果满足三个条件，如何突出显示一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4843296/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to highlight a row if three conditions are met?
提问by Pat767
If the following conditions are met:
如果满足以下条件：
For any given row between row 10 and row 100 inclusively:
对于第 10 行和第 100 行之间的任何给定行：
- The cell in column A is not empty
- The cell in column B is not empty
- The cell in column O is empty
- A列中的单元格不为空
- B列的单元格不为空
- O列中的单元格为空
I would like to highlight a specific cell (let's say A1).
我想突出显示一个特定的单元格（比如说 A1）。
Example:
例子：
I populate A10 and E10 while leaving O10 empty, then cell A1 gets highlighted. If I then populate cell O10, the highlight in cell A1 disappears.
我填充 A10 和 E10，同时将 O10 留空，然后单元格 A1 被突出显示。如果我然后填充单元格 O10，单元格 A1 中的突出显示就会消失。
I can proceed to the next row. Any row at any time should generate these actions.
我可以继续下一行。任何时候的任何行都应该生成这些动作。
Thanks!
谢谢！
回答by Charles Williams
You don't need VBA: just use conditional formatting on cell A10 with the following formula:
您不需要 VBA：只需使用以下公式在单元格 A10 上使用条件格式：
=AND(NOT(ISBLANK($A10)),NOT(ISBLANK($B10)),ISBLANK($O10))
=AND(NOT(ISBLANK($A10)),NOT(ISBLANK($B10)),ISBLANK($O10))
回答by frenchie
This will do the highlights based on the conditions you specified. When you run it, it'll stop at the first row you need to input something in column O. If you want it to keep running until row 101 and highlight all the rows, then remove then Exit Do command that's between the 2 End If statements.
这将根据您指定的条件进行突出显示。当您运行它时，它将停在您需要在 O 列中输入内容的第一行。如果您希望它一直运行到第 101 行并突出显示所有行，然后删除 2 End If 之间的 then Exit Do 命令声明。
Sub Highlight()
Dim TheRow As Integer
TheRow = 9
Application.ScreenUpdating = False 'This hides the visual process and speeds up
'the execution
Do
TheRow = TheRow + 1
If TheRow = 101 Then Exit Do
Cells(TheRow, 1).Select
Selection.Interior.Pattern = 0
Cells(TheRow, 2).Select
Selection.Interior.Pattern = 0
If Not Cells(TheRow, 1).Value = "" And Not Cells(TheRow, 2).Value = "" And Cells(TheRow, 15).Value = "" Then
If Cells(TheRow, 1).Value = "" Then
Cells(TheRow, 1).Select
Selection.Interior.Color = 656
End If
If Cells(TheRow, 2).Value = "" Then
Cells(TheRow, 2).Select
Selection.Interior.Color = 656
End If
Exit Do 'this is the line to remove if you want to highlight all cells
End If
Loop
Application.ScreenUpdating = True
End Sub
And then, create an event handler that triggers when a cell in column 15 changes. Put the following code in the module of the actual worksheet (in the VBA project explorer, double click on the sheet you want have this functionality for; don't put this in a different module!)
然后，创建一个在第 15 列中的单元格更改时触发的事件处理程序。将以下代码放在实际工作表的模块中（在 VBA 项目资源管理器中，双击您想要具有此功能的工作表；不要将其放在不同的模块中！）
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 15 Then
If Target.Row > 9 And Target.Row < 101 Then Call Highlight
End Sub
Let me know if this solution works and remember to click "accept solution" and to vote for it!
让我知道这个解决方案是否有效，记得点击“接受解决方案”并投票支持它！
Happy coding.
快乐编码。
回答by Charles Williams
OK - I misunderstood what you wanted. Here is a VBA UDF to do the checking.
Enter =Checker($A$10:$B$100,$O$10:$O$100)in cell A1, then use conditional formatting on cell A1 that is triggered when it becomes True.
好的 - 我误解了你想要的。这是一个用于检查的 VBA UDF。=Checker($A$10:$B$100,$O$10:$O$100)在单元格 A1 中输入，然后在单元格 A1 上使用条件格式，当它变为 True 时触发。
Public Function Checker(theRangeAB As Range, theRangeO As Variant) As Boolean
Dim varAB As Variant
Dim varO As Variant
Dim j As Long
varAB = theRangeAB.Value2
varO = theRangeO.Value2
Checker = False
For j = 1 To UBound(varAB)
If Not IsEmpty(varAB(j, 1)) And Not IsEmpty(varAB(j, 2)) Then
If IsEmpty(varO(j, 1)) Then
Checker = True
Exit For
End If
End If
Next j
End Function
