Just add CF to CF with a formula of

=$E5="Completed"

and set the font colour to white.

No need for code.

--

HTH

Bob Phillips

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

"lmv" wrote in message
...
Hi Bob,
You probably noticed E is set to green in 3rd CF
=$E$5="Completed" Green

my problem is I don't know how to add to the formula to turn C5 to white
interior and font when =$E$5="Completed" Green is chosen. My problem seems
to
always be not knowing what seperates the statements within one formula.

=$E$5="Completed" Green : c.Offset(0, -2).Font.ColorIndex = 2 :c.Offset(0,
-2).Interior.ColorIndex = 2

And since at the point where completed is chosen in E the 3rd CF on C5 is

=AND(C4-TODAY()<=1) RED

is there a way to write UNLESS E5 value ="completed"

Or is there a way for this to be a macro so that each cell CF doesn't need
to be set and it can be set by range? Sorry I seem to know what I want to
happen I just don't know the coding to impliment it... :(

Thanks!




"Bob Phillips" wrote:

I have been re-reading this thread and I am wondering why Peter
suggested
code in the first place. Although you want to add more actions, it is to
a
condition that already is tested for. In other words,. why don't you
just
change the CF for Completed to set the font colour to green as well as
the
cell colour?

--

HTH

Bob Phillips

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

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