Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default fill a cell color using date in one cell and "yes/no" in another c

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!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default fill a cell color using date in one cell and "yes/no" in another c

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 FormatConditional 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


"dinocm" wrote in message
...
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!



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default fill a cell color using date in one cell and "yes/no" in another c

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


"T. Valko" wrote in message
...
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 FormatConditional 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


"dinocm" wrote in message
...
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!





  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default fill a cell color using date in one cell and "yes/no" in anoth

"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" wrote:

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


"T. Valko" wrote in message
...
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 FormatConditional 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


"dinocm" wrote in message
...
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!






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default fill a cell color using date in one cell and "yes/no" in anoth

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" wrote in message
...
"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" wrote:

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


"T. Valko" wrote in message
...
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 FormatConditional 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


"dinocm" wrote in message
...
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!









  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default fill a cell color using date in one cell and "yes/no" in anoth

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" wrote:

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" wrote in message
...
"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" wrote:

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


"T. Valko" wrote in message
...
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 FormatConditional 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


"dinocm" wrote in message
...
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!








  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default fill a cell color using date in one cell and "yes/no" in anoth

You're welcome!

--
Biff
Microsoft Excel MVP


"dinocm" wrote in message
...
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" wrote:

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" wrote in message
...
"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" wrote:

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


"T. Valko" wrote in message
...
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 FormatConditional 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


"dinocm" wrote in message
...
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!










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Incorporating Cell color fill in an "if" logical function? George_Sky Excel Worksheet Functions 46 April 4th 23 02:18 PM
How do I change the fill color of a cell using an "IF" function donschap Excel Worksheet Functions 6 March 9th 10 04:48 PM
How to change text color in a cell "comment" in Office 2007? conductor Excel Worksheet Functions 1 June 2nd 07 02:50 AM
Excel needs multiple "Fill color" buttons for faster coloring. SC0718 Setting up and Configuration of Excel 1 May 23rd 07 01:37 PM
Complex if test program possible? If "value" "value", paste "value" in another cell? jseabold Excel Discussion (Misc queries) 1 January 30th 06 11:01 PM


All times are GMT +1. The time now is 03:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"