conditional formatting based on a different cell

[Picodillo]

In column A I have "Date Started" dates. In Column B I have "Date Completed"
Dates. I have conditional formatting already set up to highlight column A
red if it is more than 7 days old, but I want to make a condition for Column
A to be green if there is anything entered in column B.

 

[Jacob Skaria]

1. Select ColumnA

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
=AND(A1<>"",B1="",A1<TODAY()-7)
Click Format Button>Pattern and select your color (say Red)

4. Click on Add button.

5. For Condition2>Select 'Formula Is' and enter the below formula
=AND(A1<>"",B1<>"")
Click Format Button>Pattern and select your color (say Green)

6. Hit OK

If this post helps click Yes

 

[Max]

Select col A (with A1 active), then apply CF using Formula Is:
=AND(ISNUMBER($A1),TODAY()-$A1>7,ISNUMBER(B1),B1>=$A1)

The CF expression incorporates the extra care that should be taken when
dealing with dates. The ISNUMBER checks that both cols A and B should only
contain numbers (since real dates are numbers in Excel) and averts spurious
triggers caused by inputs other than numbers (such as text) or by any blank
cells since these are evaluated by Excel as zeros in formulas. The B1>=$A1
further ensures that "dates completed" inputs are valid ones, ie these dates
should be greater than (or at least the same as) the corresponding "dates
started" in col A.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik