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!

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!

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!

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!

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!

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!