More Conditional Formatting Woes
 

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! :eek:


--
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

First, thanks to David M. for cleaning up my clumsy code. I modified
Dave P's code designed for a different application which had the range
contraint already. I modified it to expand and match the OP's range.

As for RMCGAL's dilema with getting the code to work, I guess we should
begin with what does happen when you make an entry in A2? Are you sure
the code was entered in the VBA editor as directed?

Let's verify that you first copied the entire code from the post. You
then right clicked on the tab of the sheet you wish to have this code
applied to. From the popup menu, you clicked on View Code (which then
opens the VBA Editor, with that sheet's code window open. Paste the
code in the right hand pane. Press Alt+Q to close the Editor. Make an
entry in A2 and press ENTER. Today's date should now appear in B2.

If not, what (if anything) does happen?

For macro security, you should have your setting at Medium. This
allows you to choose whether or not to allow a macro to run. ONLY run
trusted macros!

Good Luck,

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 did have to change my security to medium, just to allow the code to
save. However, when I go back to my worksheet and enter things into
column A and hit enter, nothing happens. The date cell remains empty.

???


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

Make sure that Column B is not hidden and is sufficiently wide
to accept the date. If column B is too narrow you will see pound signs (#).

I did not test my changes to code, so thought I'd better test it, it worked
except for the item at top -- but that still did something.

I've modified the code so that emptying an entire line will not cause
a failure.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If IsEmpty(Target(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

If nothing is still happening, make sure that you press the square button
in the Visual Basic Editor. Macros can't be invoked in pause mode.
It that is not the problem then in the VBE use Ctrl+G to bring up
the intermediate window and paste the following into it and hit enter
Application.EnableEvents = True
in fact as on my webpage you might as well also include
Application.DisplayAlerts = True

You will find a more complete description of this at
http://www.mvps.org/dmcritchie/excel/event.htm#problems

---
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

"rmcgal" wrote in message
...

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

I am not quite sure what part of that did the trick...but it is working
now!

Bruce and David...you guys rock!

Thank you! Thank you! Thank you!!

:) :) :)


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

Thanks for the feedback,
The fact that it works is the main thing.

It would have be nice to know what the problem was, but knowing
what you changed to make something work will also improve over
time. It was probably a combination of things. Anyway I am rewriting
the topics concerning Problems on my event.htm and getstarted.htm
webpages.
http://www.mvps.org/dmcritchie/excel/event.htm#problems

---
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

"rmcgal" wrote in message
...

I am not quite sure what part of that did the trick...but it is working
now!

Bruce and David...you guys rock!

Thank you! Thank you! Thank you!!

:) :) :)


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