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=518482

Cell Colour based on text in Range of cells
 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
dim i as integer
dim cell
If Not Intersect(Target, Range("G14:S14")) Is Nothing Then

Range("G14:S14").select
for each cell in selection
if cell.Text = "N/A" Or cell.text= "date value" Then
Range("F14").Interior.ColorIndex = 4
Else
Range("F14").Interior.ColorIndex = 0
End If
next
End If

End Sub


http://www.geocities.com/excelmarksway for Color Utilities


"viewmaster" wrote:


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=518482