如果满足三个条件如何突出显示一行？

发布于 2024-10-15 03:29:53 字数 294 浏览 5 评论 0原文

如果满足以下条件：

对于第 10 行和第 100 行之间的任何给定行：

A 列中的单元格不为空
B 列中的单元格不为空
O 列中的单元格为空

我想突出显示特定单元格（假设是A1）。

示例：

我填充 A10 和 E10，同时将 O10 留空，然后单元格 A1 会突出显示。如果我随后填充单元格 O10，单元格 A1 中的突出显示就会消失。

我可以继续下一行。任何时间任何行都应该生成这些操作。

谢谢！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寂寞美少年 2024-10-22 03:29:53

这将根据您指定的条件进行突出显示。当您运行它时，它将停止在您需要在 O 列中输入某些内容的第一行。如果您希望它继续运行直到第 101 行并突出显示所有行，则删除 2 End If 之间的 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

然后，创建一个事件处理程序，当第 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

让我知道此解决方案是否有效并记住点击“接受解决方案”并投票！

快乐编码。

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.

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!)

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.

回复收藏 0 原文

完美的未来在梦里 2024-10-22 03:29:53

您不需要 VBA：只需使用以下公式在单元格 A10 上使用条件格式：

=AND(NOT(ISBLANK($A10)),NOT(ISBLANK($B10)),ISBLANK($O10))< /代码>

回复收藏 0 原文

萌逼全场 2024-10-22 03:29:53

好吧 - 我误解了你想要的。这是一个用于进行检查的 VBA UDF。
在单元格 A1 中输入 =Checker($A$10:$B$100,$O$10:$O$100)，然后在单元格 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

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.

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

回复收藏 0 原文

~没有更多了~