Try this one...if should do want you want as below

Private Sub CommandButton3_Click()
Dim j As Integer
On Error GoTo Errhandler
For j = 1 To 4 ' set number columns wide you want If you have 4 names
then this is 4

If Cells(2, j).Value < "" Then ' check the row 2 for the
date is not empty
If Len(Cells(2, j).Value) = 8 Then
Cells(3, j).Value = Date ' enters today date in
row 3 for each name

If DateDiff("m", Cells(2, j).Value, Date) <= 6
Then ' checks date is less/equal than/to 6 months
Cells(4, j).Value = "VALID"
Cells(4, j).Interior.Color = vbBlue
Else
Cells(4, j).Value = "Expired"
Cells(4, j).Interior.Color = vbRed
End If
Else
MsgBox " Invalid Date Entry in Vell " &
Replace(Cells(2, j).Address, "$", "")
Cells(4, j).Value = ""
Cells(2, j).Value = ""
Cells(4, j).Interior.ColorIndex = xlNone
End If

Else
Cells(4, j).Value = ""
Cells(4, j).Interior.ColorIndex = xlNone
End If
Next j
Exit Sub
Errhandler:
If Err.Number = 13 Then

MsgBox " Invalid Date Entry in Vell " & Replace(Cells(2, j).Address,
"$", "") ' for if he date is not entered right
Cells(2, j).ClearContents
Err.Clear
Else
End If

End Sub
Lanre Bex Ayoade wrote:
Please see below, this is all i want to do, I will only need to input the
current date (A3) and get either 'valid' with blue colour or Expired with red
colour in A4
A1 Ade Shola Bex Niyi
A2 3/4/06 4/1/06 23/2/06 26/2/06
A3 31/8/06 31/8/06 31/8/06 31/8/06
A4 'valid' 'Expired' 'Expired' 'Expired'



"stevebriz" wrote:


Lanre,
Is there more cells than jsut A2 -A4 you want this to "effect" to occur
on?
If so please tell me how which cells you want this to affect.
This is code that you put in a through VBA( Press ALT F11) and this
brings up the Vbeditor
You then double click where it says sheet1 in the project explorer and
paste this code in
(This is assuming you are working on sheet 1)
Whenever you change A2 the color will change
The ":valid" will checked when any cell on the sheet is changed....or
do you want this to be automatic when you open the sheet?