fill a cell color using date in one cell and "yes/no" in another c

D

dinocm

I want to use two different cells in a single row (one a date-formatted cell
& one a "Yes/No" text value cell) to display a cell fill color in a third
cell - based on the conditions (e.g., date is <= today and text is "no" -
filling third cell with red) present;

How can I do this?

Txs!
 
T

T. Valko

Try this...

A1 = some date
B1 = yes or no
C1 = fill with color if A1 = a date <=today and C1 = no

Select cell C1
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box to the right:

=AND(COUNT(A1),A1<=TODAY(),C1="no")

Click the Format button
Select the Patterns tab
Select the color of your choice
OK out
 
D

dinocm

"T. VALKO" -

Below is the result I am trying to achieve:

IF B$ IS <TODAY AND C$=NO, THEN FILL A$ AS RED;
IF B$ IS =>TODAY AND C$=NO, THEN FILL A$ AS GOLD;
IF C$=YES, THEN FILL A$ AS GREEN;

Condition ONE: Column A [cell] is filled RED when Column B [cell] entered
date is before the current date AND Column C [cell] is NO;

Condition TWO: Column A [cell] is filled GOLD when Column B [cell entered
date is either the current or a future date AND Column C [cell] is NO;

Condition THREE: Column A [cell] is filled GREEN when Column C [cell] is YES;
[NOTE: Column B data has no bearing or impact on Condition THREE]

Hope this explains it a little more clearly and I definitely hope you can
help me out!
====================================================
 
T

T. Valko

Ok, try these:

Condition 1
Formula Is: =AND(COUNT(B1),B1<TODAY(),C1="no")
Set color to RED

Condition 2
Formula Is: =AND(COUNT(B1),B1>=TODAY(),C1="no")
Set color to GOLD/YELLOW

Condition 3
Formula Is: =C1="yes"
Set color to GREEN

--
Biff
Microsoft Excel MVP


dinocm said:
"T. VALKO" -

Below is the result I am trying to achieve:

IF B$ IS <TODAY AND C$=NO, THEN FILL A$ AS RED;
IF B$ IS =>TODAY AND C$=NO, THEN FILL A$ AS GOLD;
IF C$=YES, THEN FILL A$ AS GREEN;

Condition ONE: Column A [cell] is filled RED when Column B [cell] entered
date is before the current date AND Column C [cell] is NO;

Condition TWO: Column A [cell] is filled GOLD when Column B [cell entered
date is either the current or a future date AND Column C [cell] is NO;

Condition THREE: Column A [cell] is filled GREEN when Column C [cell] is
YES;
[NOTE: Column B data has no bearing or impact on Condition THREE]

Hope this explains it a little more clearly and I definitely hope you can
help me out!
====================================================

T. Valko said:
Ooops! Typos!


All references to C1 should be B1.
Click to expand...
Click to expand...
 
D

dinocm

While waiting for a response, I tried this in the 3rd condition line:

=OR(B8>=TODAY(),B8<TODAY(),E8="YES")

Both my solution AND yours worked - but your was MUCH SIMPLER!!

Thank you for your assistance!

"dinocm"

T. Valko said:
Ok, try these:

Condition 1
Formula Is: =AND(COUNT(B1),B1<TODAY(),C1="no")
Set color to RED

Condition 2
Formula Is: =AND(COUNT(B1),B1>=TODAY(),C1="no")
Set color to GOLD/YELLOW

Condition 3
Formula Is: =C1="yes"
Set color to GREEN

--
Biff
Microsoft Excel MVP


dinocm said:
"T. VALKO" -

Below is the result I am trying to achieve:

IF B$ IS <TODAY AND C$=NO, THEN FILL A$ AS RED;
IF B$ IS =>TODAY AND C$=NO, THEN FILL A$ AS GOLD;
IF C$=YES, THEN FILL A$ AS GREEN;

Condition ONE: Column A [cell] is filled RED when Column B [cell] entered
date is before the current date AND Column C [cell] is NO;

Condition TWO: Column A [cell] is filled GOLD when Column B [cell entered
date is either the current or a future date AND Column C [cell] is NO;

Condition THREE: Column A [cell] is filled GREEN when Column C [cell] is
YES;
[NOTE: Column B data has no bearing or impact on Condition THREE]

Hope this explains it a little more clearly and I definitely hope you can
help me out!
====================================================

T. Valko said:
Ooops! Typos!

C1 = fill with color if A1 = a date <=today and C1 = no
=AND(COUNT(A1),A1<=TODAY(),C1="no")

All references to C1 should be B1.


--
Biff
Microsoft Excel MVP


Try this...

A1 = some date
B1 = yes or no
C1 = fill with color if A1 = a date <=today and C1 = no

Select cell C1
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box to the right:

=AND(COUNT(A1),A1<=TODAY(),C1="no")

Click the Format button
Select the Patterns tab
Select the color of your choice
OK out

--
Biff
Microsoft Excel MVP


I want to use two different cells in a single row (one a date-formatted
cell
& one a "Yes/No" text value cell) to display a cell fill color in a
third
cell - based on the conditions (e.g., date is <= today and text is
"no" -
filling third cell with red) present;

How can I do this?

Txs!
Click to expand...
Click to expand...
Click to expand...
 
T

T. Valko

You're welcome!

--
Biff
Microsoft Excel MVP


dinocm said:
While waiting for a response, I tried this in the 3rd condition line:

=OR(B8>=TODAY(),B8<TODAY(),E8="YES")

Both my solution AND yours worked - but your was MUCH SIMPLER!!

Thank you for your assistance!

"dinocm"

T. Valko said:
Ok, try these:

Condition 1
Formula Is: =AND(COUNT(B1),B1<TODAY(),C1="no")
Set color to RED

Condition 2
Formula Is: =AND(COUNT(B1),B1>=TODAY(),C1="no")
Set color to GOLD/YELLOW

Condition 3
Formula Is: =C1="yes"
Set color to GREEN

--
Biff
Microsoft Excel MVP


dinocm said:
"T. VALKO" -

Below is the result I am trying to achieve:

IF B$ IS <TODAY AND C$=NO, THEN FILL A$ AS RED;
IF B$ IS =>TODAY AND C$=NO, THEN FILL A$ AS GOLD;
IF C$=YES, THEN FILL A$ AS GREEN;

Condition ONE: Column A [cell] is filled RED when Column B [cell]
entered
date is before the current date AND Column C [cell] is NO;

Condition TWO: Column A [cell] is filled GOLD when Column B [cell
entered
date is either the current or a future date AND Column C [cell] is NO;

Condition THREE: Column A [cell] is filled GREEN when Column C [cell]
is
YES;
[NOTE: Column B data has no bearing or impact on Condition THREE]

Hope this explains it a little more clearly and I definitely hope you
can
help me out!
====================================================

:

Ooops! Typos!

C1 = fill with color if A1 = a date <=today and C1 = no
=AND(COUNT(A1),A1<=TODAY(),C1="no")

All references to C1 should be B1.


--
Biff
Microsoft Excel MVP


Try this...

A1 = some date
B1 = yes or no
C1 = fill with color if A1 = a date <=today and C1 = no

Select cell C1
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box to the right:

=AND(COUNT(A1),A1<=TODAY(),C1="no")

Click the Format button
Select the Patterns tab
Select the color of your choice
OK out

--
Biff
Microsoft Excel MVP


I want to use two different cells in a single row (one a
date-formatted
cell
& one a "Yes/No" text value cell) to display a cell fill color in a
third
cell - based on the conditions (e.g., date is <= today and text is
"no" -
filling third cell with red) present;

How can I do this?

Txs!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How To Have A Cell Be Blank If... 2
Can RGB fill color be automated based on 3 cell values? 2
Cell fill color in linked cells? 1
make all cells in same row match cell formatted with fill color 2
How can I sum numbers that have a gray color-fill in cell? 1
fill in date into adjacent cell automatically 2
Change Text Color in one cell based upon entry in referenced cell 3
Conditional formatting, based on a different cell? 3

Top