Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting based on a different cell
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting based on a different cell
1. Select ColumnA
2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(A1<"",B1="",A1<TODAY()-7) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =AND(A1<"",B1<"") Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Picodillo" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting based on a different cell
Select col A (with A1 active), then apply CF using Formula Is:
=AND(ISNUMBER($A1),TODAY()-$A17,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 --- "Picodillo" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting, based on a different cell | Excel Discussion (Misc queries) | |||
conditional Formatting based on a value in another cell | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting on one cell based on value in another | Excel Worksheet Functions |