Conditional Formatting for 2 Values

[Jenawade]

I've tried to work this out myself via a search of old posts with no luck. I have column F filled with dates and column G filled with either Pending or Complete. I need them both (preferred), or at least the dates in column F, to turn yellow if the date in column F has passed AND column G in that row says 'Pending'. So, if I have:

F (Date) G (Status)
2) 12/5/03 Complete
3) 2/12/04 Complete
4) 1/19/04 Pending
5) 3/17/04 Pending
6) 2/30/04 Pending
7) 1/25/04 Pending
8) 1/15/04 Complete

I need rows 4 & 7, column F (hopefully G as well) to change to yellow.

Any help would be very much appreciated!
Jen

 

[Aladin Akyurek]

Select F2:G8.
Activate Format|Conditional Formatting.
Choose Formula is for Condition 1.
Enter the following in the white box:

=N($F2)*($F2 < TODAY())*($G2="Pending")

Activate the Format button.
Choose yellow from the Patterns tab.
Click OK, OK.

 

[Greg Truby]

Looks like Aladin just beat me to the punch - and as usual he thought of something I didn't which was to check to see if there was a number there to begin with...

So just one light-hearted question: #6? 2/30/04???

 

[Positive Parrot]

Jen,

How about something like

Sub Yellow

Range("F2").Select
Do Until ActiveCell.Value = ""
If DateValue(ActiveCell.Value) < Now And ActiveCell.Offset(0, 1).Value = "Pending" Then
For i = 0 To 1
ActiveCell.Offset(0, i).Activate
With Selection.Interior
       .ColorIndex = 6
End With
Next i
ActiveCell.Offset(0, -1).Activate
End If
ActiveCell.Offset(1, 0).Activate
Loop

End Sub

Or if you set it up under the Worksheet_Activate event macro it should run whenever you select the sheet

Regards,

PP

 

[Aladin Akyurek]

...So just one light-hearted question: #6? 2/30/04???

Alas, we don't have a worksheet function comparable to IsDate in VBA for checking the date values. What I included is rather a weak test -- though it excludes empty cells effectively. However, there is a convoluted way using CELL to test dates. I posted a few times such a test. Amazingly, it never captured the attention.

 

[Jenawade]

So just one light-hearted question: #6? 2/30/04???

You wanna know the really bad part? I had to look at it a few seconds before I even realized what you meant! I guess I'm just living in my own little world here where we have a 2/30!

Thanks a ton - You guys are the greatest - God I love this site!
~Jen