HELP!!! Conditional Formatting

[T. Williams]

I have a spreadsheet which is set up to track a schedule based on dates. I
have two rows; top for a projected date (static) - bottom for an actual date
(varies).

This schedule is tracked daily, therefore I have the TODAY() function in the
bottom rows. I would like the following to happen:

NOT COMPLETE - NOT AFFECTED:
If the bottom "actual date" cell contains the TODAY() function and is equal
to or less than the top "projected date" cell, I would like the cell to be
formatted white cell/white text.

NOT COMPLETE - BEHIND SCHEDULE:
If the bottom "actual date" cell contains the TODAY() function and is
greater than the top "projected date" cell, I would like the cell to be
formatted red cell/red text.

COMPLETE - ON SCHEDULE:
If the bottom "actual date" cell contains an entered date which is equal to
or less than the top "projected date" cell, I would like the cell to be
formatted yellow cell/green text.

COMPLETE - BEHIND SCHEDULE
If the bottom "actual date" cell contains an entered date which is greater
than the top "projected date" cell, I would like the cell to be formatted
yellow cell/red text.

I'm thinking that I have too many variables for conditional formatting. I
also attempted to use the AND function within the conditional formatting and
was unsucessful. PLEASE help if you can... this schedule is what's driving
our company and it's currently a management headache. Any info is
appreciated!!!

 

[JLatham]

I don't know that you'd ever be able to do it using conventional conditional
formatting since in one case you have results based on a cell containing a
formula that returns a date (=TODAY()) while in another case you will be
evaluating a typed-in date. So the first task is to determine whether that
bottom actual date cell contains the =TODAY() formula or not.

The two code sections I've provided below will do the job for you, I think.
They are attached to the worksheet's events, so to get it all into the proper
place:
Right-click on the worksheet's name tab and choose [View Code] from the list
that comes up.
Copy all of the code below and paste it into the code module presented to you.
Make changes to the two cell addresses in both code segments to correspond
to the cells in question on the actual worksheet.
Close the VB Editor. Save the workbook. Try it out.

The section associated with the _Activate() event will make sure that the
colors are set according to the date relationships when you first choose that
sheet (you may have to choose another sheet first to see this - the
_Activate() doesn't fire if the sheet is the one selected when the book is
first opened or chosen after another workbook has been in use).
The section associated with the _Change() event will update the colors when
a change to either of the cells takes place - it ignores changes made to any
other cells on that sheet or on any other sheet in the workbook.
The two sections contain essentially the same code, they just work at
different times during your use of the workbook/worksheet.

Private Sub Worksheet_Activate()
'change these constants as required
'by using this _Activate() event, we update
'automatically anytime you first look at the sheet
Const actualDateCell = "H5" ' address
Const projectedDateCell = "D1" ' address
Const colorWhite = 2
Const colorRed = 3
Const colorYellow = 50
Const colorGreen = 6
Dim tempFormula As String

'first have to determine if actualDateCell has
'formula "=TODAY()" or something else -
'presumably a typed in date.
tempFormula = Range(actualDateCell).Formula
If tempFormula = "=TODAY()" Then
'it does have that formula
If Range(actualDateCell) <= Range(projectedDateCell) Then
'white on white
Range(actualDateCell).Font.ColorIndex = colorWhite
Range(actualDateCell).Interior.ColorIndex = xlNone
Else
'red on red
Range(actualDateCell).Font.ColorIndex = colorRed
Range(actualDateCell).Interior.ColorIndex = colorRed
End If
Else
'presumed to have typed in date
If Range(actualDateCell) <= Range(projectedDateCell) Then
'green on yellow
Range(actualDateCell).Font.ColorIndex = colorGreen
Range(actualDateCell).Interior.ColorIndex = colorYellow
Else
'red on yellow
Range(actualDateCell).Font.ColorIndex = colorRed
Range(actualDateCell).Interior.ColorIndex = colorYellow
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'change these constants as required
'by using this _SelectionChange() event, we update
'automatically anytime either of the two cells
'of interest change while the sheet is active.
'these two addresses MUST have $ symbols in them
Const actualDateCell = "$H$5" ' address
Const projectedDateCell = "$D$1" ' address
Const colorWhite = 2
Const colorRed = 3
Const colorYellow = 50
Const colorGreen = 6
Dim tempFormula As String

'see if one of our 2 cells changed value
If Target.Address <> actualDateCell And _
Target.Address <> projectedDateCell Then
'not one of our two cells of interest, exit
Exit Sub
End If
'one or the other changed...continue
'first have to determine if actualDateCell has
'formula "=TODAY()" or something else -
'presumably a typed in date.
tempFormula = Range(actualDateCell).Formula
If tempFormula = "=TODAY()" Then
'it does have that formula
If Range(actualDateCell) <= Range(projectedDateCell) Then
'white on white
Range(actualDateCell).Font.ColorIndex = colorWhite
Range(actualDateCell).Interior.ColorIndex = xlNone
Else
'red on red
Range(actualDateCell).Font.ColorIndex = colorRed
Range(actualDateCell).Interior.ColorIndex = colorRed
End If
Else
'presumed to have typed in date
If Range(actualDateCell) <= Range(projectedDateCell) Then
'green on yellow
Range(actualDateCell).Font.ColorIndex = colorGreen
Range(actualDateCell).Interior.ColorIndex = colorYellow
Else
'red on yellow
Range(actualDateCell).Font.ColorIndex = colorRed
Range(actualDateCell).Interior.ColorIndex = colorYellow
End If
End If
End Sub

 

[Bob Phillips]

Create a simple UDF,


Function IsFormula(rng As Range)
IsFormula = rng.HasFormula
End Function

use the NOT COMPLETE format as standard, and then use

=AND(IsFormula(G18),G18>$G1) - NOT COMPLETE - BEHIND SCHEDULE

=AND(NOT(IsFomula(G18)),G18<=G$1) - COMPLETE - ON SCHEDULE

=AND)NOT(IsFormula(G18)),G18>G$1) - COMPLETE - BEHIND SCHEDULE

 

[driller]

if i'll do it in simple way...i will make another row below the *actual
date*, something like a new row name like *Today()s Status*.
In this simple way, you will not confuse the today()s date with the row of
*actual date*.

hth

 

[JLatham]

Sweet. I'd forgotten about .HasFormula.

For T.Miller - there's one typo in Bob's last conditional formula: that
first ) should be ( like in all the others.

 

[T. Williams]

Bob, not sure if anyone has ever told you this, but... YOU'RE THE MAN!!!

This worked exactly how I wanted it to. Thank you so much, I really
appreciate it!