A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old July 15th 08, 03:50 AM posted to microsoft.public.excel.misc
dinocm
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!
Ads
  #2  
Old July 15th 08, 05:50 AM posted to microsoft.public.excel.misc
T. Valko
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 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


"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  
Old July 15th 08, 05:58 AM posted to microsoft.public.excel.misc
T. Valko
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 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
>
>
> "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  
Old July 15th 08, 06:38 PM posted to microsoft.public.excel.misc
dinocm
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 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
> >
> >
> > "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  
Old July 15th 08, 09:33 PM posted to microsoft.public.excel.misc
T. Valko
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 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
>> >
>> >
>> > "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  
Old July 15th 08, 11:08 PM posted to microsoft.public.excel.misc
dinocm
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 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
> >> >
> >> >
> >> > "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  
Old July 15th 08, 11:17 PM posted to microsoft.public.excel.misc
T. Valko
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 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
>> >> >
>> >> >
>> >> > "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!
>> >> >
>> >> >
>> >>
>> >>
>> >>

>>
>>
>>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Incorporating Cell color fill in an "if" logical function? George_Sky Excel Worksheet Functions 45 May 6th 10 06:42 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 05:50 AM.


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