formatting of row depending on cell value

  • Thread starter Thread starter RedBeard
  • Start date Start date
R

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.
 
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
 
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
 
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
Click to expand...

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?
 
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?
Click to expand...

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
 
Not sure what you mean.

Do you mean for the CF>Formula is:?

Please give an example.


Gord
 
Not sure what you mean.

Do you mean for the CF>Formula is:?

Please give an example.

Gord
Click to expand...

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
 
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
Click to expand...

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