change cell colour if value in column = x??

[Simon Lloyd]

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

 

[Carim]

Hi Simon,

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


Dim Cell As Range
Set Target = Range("D220")
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

 

[Peter T]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Carim]

Hi Peter,

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

Thanks a lot

Carim

 

[Simon Lloyd]

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 Lloyd]

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