I need help with this...please!

Easiest way to explain this is...

I have three columns...one is a project name...two is the start date of
the project...three is a column which will show whether the work is
being done IH (in-house), CO (contracted out) or a completion date.
That third column will only have one of the three entries at a given
time.

I need the 2nd column to automaticallly fill in the current days date
ONLY when a project name has ben entered into the 1st column.

I also need the third colum to become yellow when either IH or CO is
entered into that cell. While either of these text entries are within
the cell, I need the color to change to red, when it become 45 days or
more from the Start Date (column 2).

Then when a project becomes complete and I enter a date into that 3rd
column, I need the color to change to green.

I currently have one conditional formula correctly entered that
controls that turns the cell green upon completion date. However, I
cannot get any other formulas to correctly to all of these things
together.

Thanks in advance for any help!!

I feel like I am in a boat with only one paddle...just keep circling,
but not oing anywhere!


--
rmcgal
------------------------------------------------------------------------
rmcgal's Profile: http://www.excelforum.com/member.php...o&userid=26349
View this thread: http://www.excelforum.com/showthread...hreadid=396578

I think you will need some VBA event code to have column 2 enter today's
date when column 1 is populated. I'm sure someone can come up with the
code to accomplish this.

However, for your Conditional Formatting, you will need three
conditions, as such:

Condition 1: Formula is: =AND(OR($C1="IH",$C1="CO"),$B1<=(TODAY()-45))
Format as Red fill (I would use a bold yellow font)

Condition 2: Formula is: =AND(OR($C1="IH",$C1="CO"),$B1(TODAY()-45))
Format as Yellow fill (would use a bold black or dark blue font)

Condition 3: Formula is: =ISNUMBER($C1) Format as Green fill (again
bold black or dark blue font)

Does this work for you?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=396578

Thank you so much Bruce. Once again you came through!!! It works
wonderfully!! I have messing with that on and off all day and every
time I would get one part to work..the other would stop! This is
fabulous!

I must disagree on one thing though...it seems that the older you get
the better you ARE...not used to be!!! :)

I am still working on the current date column...the only way I was able
to come up with, changes the dates each day to reflect the current day.
I want the date to be generated when the project name is entered into
the first column and then for that date to remain the same in the
future. However, that was the least of my worries...you got me through
the hard part!!

Thanks again!!


--
rmcgal
------------------------------------------------------------------------
rmcgal's Profile: http://www.excelforum.com/member.php...o&userid=26349
View this thread: http://www.excelforum.com/showthread...hreadid=396578

Hi, RMCGAL:

I modified some code
(http://www.excelforum.com/showthread...day%27s+ date)
from Dave Peterson (thanks, Dave) to meet your needs.

This is worksheet event code, which means that it needs to be placed in
the appropriate worksheet code module, not a standard code module. To do
this, right-click on the sheet tab, select the View Code option from the
menu, and paste the code in.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:a500")) Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:

Application.EnableEvents = False
With Target
If Not IsNumeric(.Value) Then
With .Offset(0, 1)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


Note: this is set to cover the first 500 rows of your sheet. Enter a
text entry in any cell A1:A500, and TODAY'S date will automatically be
placed in the corresponding B cell. You can modify this to meet your
needs by editing the range in the code. Also, if you don't want column
AB, change that to desired column to 'read'. The Date Format can be
adjusted by changing the ".NumberFormat=" line to your desired format,
e.g.: "dddd, mmmm dd, yyyy" or "dd/mm/yy", etc.

Does this work for you?

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=396578

I don't see why you would restrict the rows, see
http://www.mvps.org/dmcritchie/excel/event.htm#autodate
modified below to enter date into Column B when Column A
is changed. As setup will not enter a date if there is
already content in the cell in Column B.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Date
Target.offset(0, 1).numberformat = "yyyy-mm-dd"
End If
End Sub
It would actually be better to format the entire Column Bwith the date format you want rather than individually
formatting each cell in Column B -- won't matter that you
enter text into row 1 for a title.



--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"swatsp0p" wrote in message
...

Hi, RMCGAL:

I modified some code
(http://www.excelforum.com/showthread...day%27s+ date)
from Dave Peterson (thanks, Dave) to meet your needs.

This is worksheet event code, which means that it needs to be placed in
the appropriate worksheet code module, not a standard code module. To do
this, right-click on the sheet tab, select the View Code option from the
menu, and paste the code in.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:a500")) Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:

Application.EnableEvents = False
With Target
If Not IsNumeric(.Value) Then
With .Offset(0, 1)
Value = Date
NumberFormat = "mm/dd/yyyy"
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


Note: this is set to cover the first 500 rows of your sheet. Enter a
text entry in any cell A1:A500, and TODAY'S date will automatically be
placed in the corresponding B cell. You can modify this to meet your
needs by editing the range in the code. Also, if you don't want column
AB, change that to desired column to 'read'. The Date Format can be
adjusted by changing the ".NumberFormat=" line to your desired format,
e.g.: "dddd, mmmm dd, yyyy" or "dd/mm/yy", etc.

Does this work for you?

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=396578

I tried using both of your codes. I am not sure what I am doing wrong,
but neither seems to work.

I had to lower my security setting just to allow them to open, but
nothing happened with eitehr code.

:(

Thanks for all of your help! Have a great day!!


--
rmcgal
------------------------------------------------------------------------
rmcgal's Profile: http://www.excelforum.com/member.php...o&userid=26349
View this thread: http://www.excelforum.com/showthread...hreadid=396578