How to highlight a row if three conditions are met?

If the following conditions are met:

For any given row between row 10 and row 100 inclusively:

• The cell in column A is not empty
• The cell in column B is not empty
• The cell in column O is empty

I would like to highlight a specific cell (let's say A1).

Example:

I populate A10 an开发者_运维知识库d E10 while leaving O10 empty, then cell A1 gets highlighted. If I then populate cell O10, the highlight in cell A1 disappears.

I can proceed to the next row. Any row at any time should generate these actions.

Thanks!

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.

You don't need VBA: just use conditional formatting on cell A10 with the following formula:

=AND(NOT(ISBLANK($A10)),NOT(ISBLANK($B10)),ISBLANK($O10))

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