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


--
viewmaster
------------------------------------------------------------------------
viewmaster's Profile: http://www.excelforum.com/member.php...o&userid=32094
View this thread: http://www.excelforum.com/showthread...hreadid=518485

Cell Colour based on text in Range of cells
 

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" wrote
in message ...

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

Cell Colour based on text in Range of cells
 

Thank you soooo much! It works a charm!

--
viewmaste
-----------------------------------------------------------------------
viewmaster's Profile: http://www.excelforum.com/member.php...fo&userid=3209
View this thread: http://www.excelforum.com/showthread.php?threadid=51848