formatting of row depending on cell value

[RedBeard]

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.

 

[FloMM2]

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

 

[Gord Dibben]

Select the rows, not just single cells to format.

Conditional Format>Formula 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

 

[RedBeard]

Select the rows, not just single cells to format.

Conditional Format>Formula 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

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?

 

[RedBeard]

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?

 

[Gord Dibben]

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

 

[Gord Dibben]

Not sure what you mean.

Do you mean for the CF>Formula is:?

Please give an example.


Gord

 

[RedBeard]

Not sure what you mean.

Do you mean for the CF>Formula is:?

Please give an example.

Gord

I meant as following:

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

Apologies for being unclear.

 

[Gord Dibben]

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

 

[RedBeard]

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

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