Highlight a row in Excel

[Stage Lamp]

I have a spreadsheet that lists sales information for every day of the year:

Row 1 =January 1 data
Row 2 = January 2 data and so forth
Row 3 = January 3 Data and so forth

Is there a way to format the row so that when excel looks at the current
date it would highlight that days sales information all teh way across?

So if today was the 2nd of january, itwould highklight that row (Row 2) but
tommorrow on the 3rd it would highlight row 3 etc

 

[JE McGimpsey]

One way:

Select rows 1:366. Choose Format/Conditional Formatting:

CF1: =ROW()=TODAY()-DATE(YEAR(TODAY()),1,0)
Format1: patterns/<your color>

 

[Stage Lamp]

JE

I tried your formula but nothing happened. Does it make a difference how
that column is formatted? I have it formatted as a date with day of the week,
month,day,year

 

[JE McGimpsey]

For me, Row 277 was formatted today. Your regular formatting doesn't
matter.

 

[Stage Lamp]

ok The first column is still listed as "a" does that make a difference?

or is there something subtle that I'm missing?

 

[JE McGimpsey]

I can't imagine what - using A1 or RC headers doesn't matter a bit to
the conditional format I provided.

Are you entering the formula in the conditional format dialog (i.e.,
Choose the Format/Conditional Format menu item, then choosing "Formula
is" from the dropdown, and entering the formula in the input box...)?

Are you selecting a format in the conditional format dialog?

 

[Stage Lamp]

I redid your formula and tried again when i look at the formula results it
applied teh conditional format to 8-23-06 instead of the current day. I
copied your formula using cut and paste to make sure it was right. Maybe I
could figure this out if you could explain to me exactly what the formula is
doing step by step?

Thanks

Jeff

 

[JE McGimpsey]

I redid your formula and tried again when i look at the formula results it
applied teh conditional format to 8-23-06 instead of the current day. I
copied your formula using cut and paste to make sure it was right. Maybe I
could figure this out if you could explain to me exactly what the formula is
doing step by step?

the only thing I can think of is that your computer's date is not set
correctly. Here's the breakdown:

=ROW()=TODAY()-DATE(YEAR(TODAY()),1,0)

1) ROW() returns the row number of the cell, e.g., for a cell on row 4,
it will return 4.

2) TODAY() returns the serial date, (e.g., 37534 for 6 Oct 2006, if
using the default MacXL 1904 date system).

3) YEAR(TODAY()) returns the year of the date (e.g., 2006)

4) DATE(2006, 1, 0) returns the serial date of the "zeroth" day of the
first month of 2006, which to XL is equivalent to the last day of the
previous month, or 31 Dec 2005 (i.e., 37255 if using the 1904 system).

5) Subtracting (4) from (2) returns the day of the year. That's 37534 -
37255 = 279 for 6 Oct 2006

6) Therefore ROW()=TODAY()-DATE(YEAR(TODAY()),1,0) will return TRUE for
a cell on row 279 (on 6 Oct 2006), and FALSE for cells on every other
row.

7) So row 279 should have the conditional format applied.