|First, a formula (e.g. an IF statement) that is placed in a cell cannot format a cell. A formula in a cell can only return a result in that cell.|
However, a formula used in Conditional Formatting can be used to format a cell or range of cells as long as it evaluates to TRUE. Whenever it evaluates to TRUE, the Conditional Format feature applies the format to the range.
re: what i am needing is for it to do all the fields that do have data filed in and to be automatic when the file is opened.
Where what your stating only does the field that is selected...
True, which is why I said to select all of the rows that contain your data. You can even select the entire sheet and apply the CF to every row if you choose.
and has to be applied manually every time it is open.
Not true. Conditionally Formatting remains in effect when the workbook is saved.
re: am having difficulty getting the CF working as u meantion.
Since you are having trouble with the concept of the example I offered, let's try it with the specific data you used in your last response.
To test this, please set up a worksheet just like mine so that there is no confusion.
My worksheet looks like this:
1 11/03/10 7:56
2 Expiry Date Days
3 09/25/11 322
4 06/06/11 213
5 08/01/11 268
6 12/25/10 52
7 10/30/10 -3
1 - Select Rows 3:10. I am purposely selecting blank rows to show you that you can apply CF to a larger range for future use.
Now, we are going to do the CF in 2 steps so you can see how the evaluation of the formula used impacts the CF.
2 - Click Conditional Formatting...New Rule
3 - Choose "Use a formula..."
4 - Enter =$E3 < 31
Note that in the Applies To: field it says =$3:$10. That means that formula (=$E3 < 31) is applied Row by Row and evaluates to TRUE whenever the value in Column E is less than 31. In other words, even though you don't see it, in Row 4, the formula is actually =$E4 < 31. In Row 5 it's =$E5 < 31. For each Row where it evaluates to TRUE, it formats the Row.
5 - Choose a format (e.g. Red Fill)
6 - OK your way out.
The Red fill format should now be applied to Rows 7:10 since they are all less than 31.
Now, we don't want the Rows with nothing in Column E to be formatted, so let's fix that:
7 - Choose CF...Manage Rules
8 - Double click the formula and change it to:
=AND($E3 <> "", $E3 < 31)
This will only evaluate to TRUE when there is a value in Column E and it is less than 30.
With that formula, only Row 7 should be Red.
9 - With Rows 3:10 selected, Click CF...Manage Rules...New Rule
10 - Enter: =AND($E3 <> "", $E3 < 61)
11 - Format it as Yellow fill
12 - Since Rules are applied in order, use the Down Arrow to move the New Rule (< 61) down below.
13 - OK your way out.
Row 6 should be Yellow, Row 7 should be Red.
14 - Continue adding rules and positioning them in the right order until you have all of your date ranges covered.