formatting of row depending on cell value

Hello,

I would like to accomplish the following:

If a cell in Column A corresponds to a certain date, I would like to
have the row that cell is located on to be formatted according to my
wishes, in this case, a different fill colour (non-white) and a
different text colour (non-black).

Is this possible and if so, how?

Thanks.

Redbeard,
This is what I came up with:
If you can add a helper column.
Then in the first cell in the row -
Add conditional formatting
Condition 1
"Formula Is" "=$A$3=$B$3"
Select "Format" button,
"Font" tab select "Color:" select desired color of text.
"Patterns" tab select the desired color of cell.

In the above example, "$B$3" is my helper column. It could be next to your A
column, or at the other end. Edit it accoding to your spreadsheet.

hth

"RedBeard" wrote:

Hello,

I would like to accomplish the following:

If a cell in Column A corresponds to a certain date, I would like to
have the row that cell is located on to be formatted according to my
wishes, in this case, a different fill colour (non-white) and a
different text colour (non-black).

Is this possible and if so, how?

Thanks.
.

Select the rows, not just single cells to format.

Conditional FormatFormula is: =$A1=DATEVALUE("12/25/2009")

Format to a pattern and OK your way out.

December 25th row will be colored.

Depends upon your short date format in Windows settings.

Mine is mm/dd/yyyy.............yours may be dd/mm/yyyy


Gord Dibben MS Excel MVP

On Tue, 17 Nov 2009 05:45:01 -0800 (PST), RedBeard wrote:

Hello,

I would like to accomplish the following:

If a cell in Column A corresponds to a certain date, I would like to
have the row that cell is located on to be formatted according to my
wishes, in this case, a different fill colour (non-white) and a
different text colour (non-black).

Is this possible and if so, how?

Thanks.

On Nov 17, 7:08*pm, Gord Dibben gorddibbATshawDOTca wrote:
Select the rows, not just single cells to format.

Conditional FormatFormula is: *=$A1=DATEVALUE("12/25/2009")

Format to a pattern and OK your way out.

December 25th row will be colored.

Depends upon your short date format in Windows settings.

Mine is mm/dd/yyyy.............yours may be dd/mm/yyyy

Gord Dibben *MS Excel MVP

On Tue, 17 Nov 2009 05:45:01 -0800 (PST), RedBeard wrote:
Hello,

I would like to accomplish the following:

If a cell in Column A corresponds to a certain date, I would like to
have the row that cell is located on to be formatted according to my
wishes, in this case, a different fill colour (non-white) and a
different text colour (non-black).

Is this possible and if so, how?

Thanks.

Gord,

Thank you for your reply, however, it doesn't seem to be working.
I have my dates in this format: 2009-12-31

I tried changing the DATEVALUE to this, but it doesn't trigger.
I looked at the help file and it states that it has to be in either
mm/dd/yyyy or dd/mm/yyyy, however, this is not an option for me.
Any ideas?

On Nov 18, 12:33*pm, RedBeard wrote:
On Nov 17, 7:08*pm, Gord Dibben gorddibbATshawDOTca wrote:



Select the rows, not just single cells to format.

Conditional FormatFormula is: *=$A1=DATEVALUE("12/25/2009")

Format to a pattern and OK your way out.

December 25th row will be colored.

Depends upon your short date format in Windows settings.

Mine is mm/dd/yyyy.............yours may be dd/mm/yyyy

Gord Dibben *MS Excel MVP

On Tue, 17 Nov 2009 05:45:01 -0800 (PST), RedBeard wrote:
Hello,

I would like to accomplish the following:

If a cell in Column A corresponds to a certain date, I would like to
have the row that cell is located on to be formatted according to my
wishes, in this case, a different fill colour (non-white) and a
different text colour (non-black).

Is this possible and if so, how?

Thanks.

Gord,

Thank you for your reply, however, it doesn't seem to be working.
I have my dates in this format: *2009-12-31

I tried changing the DATEVALUE to this, but it doesn't trigger.
I looked at the help file and it states that it has to be in either
mm/dd/yyyy or dd/mm/yyyy, however, this is not an option for me.
Any ideas?

Addendum, I got it working.

However, an additional question, how do I combine multiple dates in
the same formula?

Dates formatted as 2009-12-31 are just that........formatted dates.

The underlying value will still be what your short date is in Regional
Settings in Windows.

The formula is: =$A1=DATEVALUE("12/25/2009")

works for me in Excel 2003 and 2007 because my short date is mm/dd/yyyy

Are your dates maybe simply text and not real dates?

In that case, either convert to real dates or look for the text
"2009-12-31" without the DATEVALUE


Gord

On Wed, 18 Nov 2009 03:33:12 -0800 (PST), RedBeard wrote:

On Nov 17, 7:08*pm, Gord Dibben gorddibbATshawDOTca wrote:
Select the rows, not just single cells to format.

Conditional FormatFormula is: *=$A1=DATEVALUE("12/25/2009")

Format to a pattern and OK your way out.

December 25th row will be colored.

Depends upon your short date format in Windows settings.

Mine is mm/dd/yyyy.............yours may be dd/mm/yyyy

Gord Dibben *MS Excel MVP

On Tue, 17 Nov 2009 05:45:01 -0800 (PST), RedBeard wrote:
Hello,

I would like to accomplish the following:

If a cell in Column A corresponds to a certain date, I would like to
have the row that cell is located on to be formatted according to my
wishes, in this case, a different fill colour (non-white) and a
different text colour (non-black).

Is this possible and if so, how?

Thanks.

Gord,

Thank you for your reply, however, it doesn't seem to be working.
I have my dates in this format: 2009-12-31

I tried changing the DATEVALUE to this, but it doesn't trigger.
I looked at the help file and it states that it has to be in either
mm/dd/yyyy or dd/mm/yyyy, however, this is not an option for me.
Any ideas?

Not sure what you mean.

Do you mean for the CFFormula is:?

Please give an example.


Gord

On Wed, 18 Nov 2009 04:55:39 -0800 (PST), RedBeard wrote:

Addendum, I got it working.

However, an additional question, how do I combine multiple dates in
the same formula?

On Nov 18, 6:27*pm, Gord Dibben gorddibbATshawDOTca wrote:
Not sure what you mean.

Do you mean for the CFFormula is:?

Please give an example.

Gord

On Wed, 18 Nov 2009 04:55:39 -0800 (PST), RedBeard wrote:
Addendum, I got it working.

However, an additional question, how do I combine multiple dates in
the same formula?

I meant as following:

=$A1=DATEVALUE("12/25/2009") + DATEVALUE("12/31/2009") + etcetcetc

Apologies for being unclear.

First of all, you can't have more than one date in a cell unless you enter
them as text.

Are you saying A1 will have a varying date?

Do you want to format the various dates a different color?

If so, you make a rule for each Date.

If you want to format various dates to same color you could use the OR
function.

=OR($A1=DATEVALUE("12/25/2009"),$A1=DATEVALUE("12/31/2009"),$A1=DATEVALUE("1/7/2010"))

up to 7 dates.


Gord


On Thu, 19 Nov 2009 02:29:35 -0800 (PST), RedBeard wrote:

On Nov 18, 6:27*pm, Gord Dibben gorddibbATshawDOTca wrote:
Not sure what you mean.

Do you mean for the CFFormula is:?

Please give an example.

Gord

On Wed, 18 Nov 2009 04:55:39 -0800 (PST), RedBeard wrote:
Addendum, I got it working.

However, an additional question, how do I combine multiple dates in
the same formula?

I meant as following:

+ DATEVALUE("12/31/2009") + etcetcetc

Apologies for being unclear.

On Nov 19, 7:47*pm, Gord Dibben gorddibbATshawDOTca wrote:
First of all, you can't have more than one date in a cell unless you enter
them as text.

Are you saying A1 will have a varying date?

Do you want to format the various dates a different color?

If so, you make a rule for each Date.

If you want to format various dates to same color you could use the OR
function.

=OR($A1=DATEVALUE("12/25/2009"),$A1=DATEVALUE("12/31/2009"),$A1=DATEVALUE("1/7/2010"))

up to 7 dates.

Gord

On Thu, 19 Nov 2009 02:29:35 -0800 (PST), RedBeard wrote:
On Nov 18, 6:27*pm, Gord Dibben gorddibbATshawDOTca wrote:
Not sure what you mean.

Do you mean for the CFFormula is:?

Please give an example.

Gord

On Wed, 18 Nov 2009 04:55:39 -0800 (PST), RedBeard wrote:
Addendum, I got it working.

However, an additional question, how do I combine multiple dates in
the same formula?

I meant as following:

+ DATEVALUE("12/31/2009") + etcetcetc

Apologies for being unclear.

Unclear again, my mistake.
However, you provided me with the solution in #2 so I'm now sorted,
thanks for all the effort.