More Conditional Formatting Woes

[rmcgal]

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!

[swatsp0p]

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?

[rmcgal]

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

[swatsp0p]

Hi, RMCGAL:

I modified some code (http://www.excelforum.com/showthread...today%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.

[vba]
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
[/vba]

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 A>B, 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

[David McRitchie]

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" <swatsp0p.1txfmd_1124312723.7481@excelforum-nospam.com> wrote in message
news:swatsp0p.1txfmd_1124312723.7481@excelforum-nospam.com...
>
> Hi, RMCGAL:
>
> I modified some code
> (http://www.excelforum.com/showthread...today%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
> A>B, 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
>

[rmcgal]

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

[swatsp0p]

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

[rmcgal]

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.

???

[David McRitchie]

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" <rmcgal.1tyquj_1124373929.0529@excelforum-nospam.com> wrote in message
news:rmcgal.1tyquj_1124373929.0529@excelforum-nospam.com...
>
> 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
>

[rmcgal]

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

[David McRitchie]

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" <rmcgal.1tz7if_1124395512.4882@excelforum-nospam.com> wrote in message
news:rmcgal.1tz7if_1124395512.4882@excelforum-nospam.com...
>
> 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
>