How to use the Worksheet_Change event for Conditional Formatting?

Question

I have a worksheet which contains a list of tasks, one on each row. Column A is the task name, column B is the date when it must be done and Column C is the person who must do it. Column D is used to indicate when it's been done. If this column contains anything, then the background colour for whole row should be grey, otherwise it should be white.

I'm thinking the worksheet_change event is the best way to handle this. I suppose I could use Conditional Formatting, but that seems to be vulnerable to breaking if cells get dragged around - I need this to be as "bullet-proof" as possible!

In pseudo-code, I'm trying to achieve the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target includes a cell in column "D"
        If "D" is not empty
            Set entire row background to grey
        Else
            Set entire row background to white
        End If
    End If
End Sub

Can anybody give me any pointers about the best way to implement this? Am I even on the right lines, or is there a better way?

Answer 1

I think you can use the following condition on every cell:

=INDIRECT("$D" & ROW())>0

I did some copy/pastes and dragged the cells around, and the conditional format did not break.

Answer 2

With Conditional Formatting:

Go to Tools->Options->General and activate the R1C1 reference style

Condition: =ISEMPTY(RC4)

With VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim found As Range
Dim cell As Range
Dim colStart As Integer
Dim colEnd As Integer

    Set found = Intersect(Target, Me.Columns(4))
    colStart = 1
    colEnd = 4

    If Not found Is Nothing Then
        For Each cell In found
            With Me.Range(Me.Cells(cell.Row, colStart), Me.Cells(cell.Row, colEnd)).Interior
                If IsEmpty(cell) Then
                    .ColorIndex = 15
                Else
                    .ColorIndex = xlNone
                End If
            End With
        Next cell
    End If

    Set found = Nothing
End Sub

I recommend using Conditional Formatting