Conditional formatting of dates in two cells using Excel 2007

  • Thread starter Thread starter lemur99
  • Start date Start date
L

lemur99

Hi,

I've got a spreadsheet set up as:
Column A - task
Column B - Expected completion date
Column C - Actual completion date

and I want to set it up so that all tasks that have not yet been completed
but should have been are highlighted in red (that is, for example, if B2<
todays date and C2 is blank then A2 is highlighted red).

I've been messing about but can't seem to get it right in the conditional
formatting - any suggestions?

thanks
 
Select cell A2
Select conditional format -> formula
Enter the following forumla
=AND(B2<TODAY(),C2="")
Set the required format then OK/apply etc until exit conditional format
Copy Cell A2
Paste Special -> Formats to the remaining cells to have the conditional
format.

In lieu of copy -> paste special you can also select the entire range to
format before opening conditional format just so long as you ensure that A2
is the active cell (The first cell in the selection or one that stays white
in the selection). You then enter the formula as if only applying it to only
one cell A2 exactly as above.
 
1. Select the Range (say A1:A10) or column A. Please note that the cell
reference A1 mentioned in the formula is the active cell in the selection.
Active cell will have a white background even after selection

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
=AND(B1<TODAY(),C1="",A1<>"",B1>0)

4. Click Format Button>Pattern and select your color (say Red)

5. Hit OK



If this post helps click Yes
 
Back
Top