Cell Colour based on text in Range of cells

[viewmaster]

Hi!

I'm not too familiar with VBA, so i was hoping someone could please
help me!

I want to change the background colour of a single cell, based on the
text in a range of cells (which can be one of two options.) I want the
cell colour of F14 to change if *all* the cells in G14:S14 contain
either "N/A" or a date timestamp. This is what i tried out:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer


If Not Intersect(Target, Range("G14:S14")) Is Nothing Then
If Range("G14:S14").Text = "N/A" Or "date value" Then

Range("F14").Interior.ColorIndex = 4
Else
Range("F14").Interior.ColorIndex = 0
End If
End If

End Sub

This pretty much does the job, but the Or condition doesnt work. Any
help would be much appreciated!! Thanks!!

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bOk as Boolean, cell as Range
Dim icolor As Integer
If Not Intersect(Target, Range("G14:S14")) Is Nothing Then
bOk = True

for each cell in Range("G14:S14")
if not isdate(cell.Value) then
if cell.Text <> "N/A" then
bOK = False
exit for
end if
end if
Next
If bOk then
Range("F14").Interior.ColorIndex = 4
Else
Range("F14").Interior.ColorIndex = 0
End If
End if

End Sub

 

[viewmaster]

Thank you soooo much! It works a charm!