IF completed THEN invisible

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

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]

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)

 

[Guest]

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]

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)