IF completed THEN invisible
 

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!

IF completed THEN invisible
 

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!

IF completed THEN invisible
 

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!

IF completed THEN invisible
 

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!

IF completed THEN invisible
 

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!

IF completed THEN invisible
 

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!

IF completed THEN invisible
 

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!

IF completed THEN invisible
 

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!

IF completed THEN invisible
 

Thanks I think I have it now... but this will make the 4th CF If I use your
add in when I give the WB to to someone else do I have to pkg the add in
with the
WB in order for the addtional CF's to work?

=AND($C4-TODAY()=15,C4-TODAY()<=30)
=AND($C4-TODAY()=2,C4-TODAY()<=14)
=AND($C4-TODAY()<=1)
=$E5="Completed"

Thanks very much!!


"Bob Phillips" wrote:

Just add CF to CF with a formula of



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!

IF completed THEN invisible
 

NO, because the other CFs apply to column E, this applies to column C.

--

HTH

Bob Phillips

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

"lmv" wrote in message
...
Thanks I think I have it now... but this will make the 4th CF If I use
your
add in when I give the WB to to someone else do I have to pkg the add in
with the
WB in order for the addtional CF's to work?

=AND($C4-TODAY()=15,C4-TODAY()<=30)
=AND($C4-TODAY()=2,C4-TODAY()<=14)
=AND($C4-TODAY()<=1)
=$E5="Completed"

Thanks very much!!


"Bob Phillips" wrote:

Just add CF to CF with a formula of



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!