Thank you for the help ... this is working partially but because I already
have conditional formatting in these date cells (C) the background is red
when it is overdue and when it is changed in (E) to "completed" this formula
will not change the background. I don't know how to set the conditional
formatting to false before I impliment the formula given. Another glitch I
am finding if I use a drop down in E and "pending", "overdue", "not started"
should all display but if I make the mistake of going to "completed" it
changes the font to white but doesn't change/ revert back to black when I go
to the other options. Once again I don't know how to write it...

I have tried interior.colorindex =2 but it didn't work.

I tried:
If UCase(c) = "COMPLETED" Then
c.Offset(0, -2).Font.ColorIndex = 2
End If

If UCase(c) = "Pending" Then
c.Offset(0, -2).Font.ColorIndex = 1
End If

If UCase(c) = "Overdue" Then
c.Offset(0, -2).Font.ColorIndex = 1
End If

Next

Won't work either... any other steps I can try...?
Thanks!

"Bob Phillips" wrote:

Maybe you have to declare it

Sub worksheet_Change(ByVal target As Range)
Dim c As Range
Set target = Range("E5:E200")
For Each c In target
If UCase(c) = "COMPLETED" Then
c.Offset(0, -2).Font.ColorIndex = 2
End If
Next
End Sub

I allows for multiple cells, that is why it iterates through each c (c bing
a single cell) in the target, but only if they are within E5:E200, and if c
is the string required, it changes the cell 2 columns to the left to white.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"lmv" wrote in message
...
Thanks, but I get compile errors on line

For Each c In target

Also, would you mind defining what the code means with comments?
c.Offset(0, -2).Font.ColorIndex = 2
Does this mean that it is going to look at the column -2 which would be C
and change the font to white?

Thanks


"PeterAtherton" wrote:

Imv

Right-click on the sheet tab and choose Viewcode. Copy this code into
the VB
editor
It turns the dates white when column E shows completed

Sub worksheet_Change(ByVal target As Range)
Set target = Range("E5:E200")
For Each c In target
If UCase(c) = "COMPLETED" Then
c.Offset(0, -2).Font.ColorIndex = 2
End If
Next
End Sub

Regards
Peter

"lmv" wrote:

I don't know if this needs to be VBA condition a macro or a
conditional
format on the cell or ?? I don't know how to format what I want to do.

If the word "completed" is in cell E5 (actually in the E Column)
Then then date in the corresponding row C cell is invisible or text is
white
so it won't show)
I already have 3 conditional formats in these columns
=$E$5="Overdue" RED
=$E$5="Pending" Orange
=$E$5="Completed" Green
=====================================

=AND(C4-TODAY()=15,C4-TODAY()<=30) Yellow Back
=AND(C4-TODAY()=2,C4-TODAY()<=14) Orange Back
=AND(C4-TODAY()<=1) RED
======================================

Please help me write the format... Thanks!