Cell Colour based on text in Range of cells

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

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

--
Regards,
Tom Ogilvy

"viewmaster" <viewmaster.242e1o_1141340103.8546@excelforum-nospam.com> wrote
in message news:viewmaster.242e1o_1141340103.8546@excelforum-nospam.com...
>
> 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!!
>
>
> --
> viewmaster
> ------------------------------------------------------------------------
> viewmaster's Profile:
http://www.excelforum.com/member.php...o&userid=32094
> View this thread: http://www.excelforum.com/showthread...hreadid=518485
>

Thank you soooo much! It works a charm!!