conditional format

T

TC

I have a conditional formatting warning on some cells so that if the date in
B3 is older than today the background goes red

Formula is = (TODAY()-B3)>0

How would I write this so that the action is to go red unless there is a *
on cell B10 ?

Thanks
 
T

TC

Tina - works a treat - thank you.

OK, one step further. Can I use conditional formatting to turn the
background from red to green is there is a * in B10 - or is this sort of
multiple formats too much for it (using Excel97)
 
T

tina

Hi TC
To do this you need to do one conditioning format for red then select add>>
button and conditioning 2 will appear and you can enter formula where
resulting formatting is green
=AND((TODAY()-B3)>0,B10<>"*") conditioning1 formula
=AND((TODAY()-B3)>0,B10="*") conditioning2 formula
I presume this is available in excel97 as I am using excel2000
Tina
 
T

TC

Tina - your a star- many thanks

Tobit
Hi TC
To do this you need to do one conditioning format for red then select
add>>
button and conditioning 2 will appear and you can enter formula where
resulting formatting is green
=AND((TODAY()-B3)>0,B10<>"*") conditioning1 formula
=AND((TODAY()-B3)>0,B10="*") conditioning2 formula
I presume this is available in excel97 as I am using excel2000
Tina
Click to expand...
 
D

DONNA

Tina I have a problem and NEED HELP IMMEDIATELY. I would sincerely
appreciate your any help you can give me.

I am creating this spreadsheet. That shows dates for:
1. Completed on time (yellow)
2. Completed ahead of target date (green)
3. Past Target Date (red)
I NEED THE "COMPLETION DATE" column NOT to turn ANY color if there is no
date in the column AND TO Turn a color (yellow, green, red) if there is a
date in the "completion date" column.

It seems to me that I am needing 4 conditioning formatting:
PLEASE HELP AND TELL ME WHAT THE FORMULAS COULD BE.
 
R

Ragdyer

Would you accept a suggestion from someone other then Tina?

Let's say that the completion date column is G, and the target date column
is D.
Data of concern is in G2 to G50 and D2 to D50.

Select G2 to G50, with the focus in G2 (colored white), then:
<Format> <ConditionalFormat>
Change "Cell Value Is" to "Formula Is",
And enter this formula in the window:

=AND(G2<>"",G2=D2)

Then click on <Format>, and choose a yellow format (font or pattern),
Then <OK>.

Then click on <Add> for your second condition.

Again change "Cell Value Is" to "Formula Is",
And enter this formula in the window:

=AND(ISNUMBER(G2),G2<>"",G2>D2)

Then click on <Format>, and choose a red format (font or pattern),
Then <OK>.

Then click on <Add> again for your third condition.

Again change "Cell Value Is" to "Formula Is",
And enter this formula in the window:

=AND(G2<>"",G2<D2)

Then click on <Format>, and choose a green format (font or pattern),
Then <OK> <OK>.

So, you now have set your 3 conditions to color the cells when Column G is
not empty, and when Column G does not contain text.
 
Top