Hi all,
I'm trying to get a cell in Colimn V to change colour if the value 5 i
found in a particular row....so if cell D2 contained value 5 then V
must turn yellow (or some such colour) if F2 contained 5 then cell V
will be yellow etc. every other column from D up to column U for aroun
30 rows, if the value of 5 is removed or is not present then the colou
of the cell should remain or get changed back to white. This is what
have been working with but of course its doesnt work!!!!

Any ideas?
Thanks,
Simon

Dim rng As Range
If Range("$D$2:$D$40") Or Range("$F$2:$F$40") O
Range("$H$2:$H$40").Value < 5 Then
ElseIf Range("$D$2:$D$40") Or Range("$F$2:$F$40") O
Range("$H$2:$H$40").Value = 5 Then
rng = rng("V2:V40")
With rng '("V2:V40")
.Select
.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, _
Formula1:="=NOT(ISBLANK(V2))"
With .FormatConditions(1).Interior
.ColorIndex = 44
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End With
End If
End Su

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=52526

Hi Simon,

Give a try to following :
Private Sub Worksheet_Change(ByVal Target As Range)


Dim Cell As Range
Set Target = Range("D2:D20")
If Target Is Nothing Then
Exit Sub
Else
For Each Cell In Target
If Cell.Value < "" Then
Cell.Offset(0, 18).Range("A1").Interior.ColorIndex = 6
End If
If Cell.Value = "" Then
Cell.Offset(0, 1).Range("A1").Interior.ColorIndex = xlNone
End If
Next Cell
End If
End Sub

HTH
Carim

Hi Simon,

You could do this with a Conditional format.

Select cell V2, in the CF dialog -

Formula Is : =MATCH(5,D2:U2,0)

Apply your pattern yellow format

Copy V2 down

Regards,
Peter T

"Simon Lloyd"
wrote in message
...

Hi all,
I'm trying to get a cell in Colimn V to change colour if the value 5 is
found in a particular row....so if cell D2 contained value 5 then V2
must turn yellow (or some such colour) if F2 contained 5 then cell V2
will be yellow etc. every other column from D up to column U for around
30 rows, if the value of 5 is removed or is not present then the colour
of the cell should remain or get changed back to white. This is what i
have been working with but of course its doesnt work!!!!

Any ideas?
Thanks,
Simon

Dim rng As Range
If Range("$D$2:$D$40") Or Range("$F$2:$F$40") Or
Range("$H$2:$H$40").Value < 5 Then
ElseIf Range("$D$2:$D$40") Or Range("$F$2:$F$40") Or
Range("$H$2:$H$40").Value = 5 Then
rng = rng("V2:V40")
With rng '("V2:V40")
Select
FormatConditions.Delete
FormatConditions.Add _
Type:=xlExpression, _
Formula1:="=NOT(ISBLANK(V2))"
With .FormatConditions(1).Interior
ColorIndex = 44
Pattern = xlSolid
PatternColorIndex = xlAutomatic
End With
End With
End If
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=525260

Hi Peter,

Excellent idea ...
I never thought of combining CF with the match function ...
I will use from now on ...

Thanks a lot

Carim

Thanks for the replies, i will put them into practice when i am next i
work and post back here the results.......thanks for your time an
trouble!

Regards,

Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=52526

Thanks for the replies, i will put them into practice when i am next i
work and post back here the results.......thanks for your time an
trouble!

Regards,

Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=52526