Conditional Formatting Whole Row

[Mike]

Morning All,

Using Windows and Excel XP.

I have a worksheet that has the dates of this month in A1:A31 and other data
in B1:F31. I have set up a conditonal format: cell value
equal to =TODAY() to have the current date (December 6) in
A1:A31 to highlight in yellow. What I would like to do is not only
highlight the current date but also all the data in that particular row.

For example, in A1:A31, December 6, 2003 (cell A6) is highlighted in yellow.
How can I get the entire row (A6:F6) to be highlighted in yellow. Is it
possible to do that?

Thanks in advance,

Michael

 

[Dave Peterson]

Select A1:F31 (or even more rows???)

Then Format|conditional formatting
Formula is
=$a1=today()

the $A means to only look at column A.

And write your formula for the active cell's row. If you were on row 7 (even
though a bunch of cells are selected), it would be =$a7=today()

 

[Mike]

Hi Dave,
I understand the first part, about selecting A1:F31 and conditonal
formatting =$a1=today() but I'm not sure how to write the formula for it to
select the whole row. Would it be an if statement, like
=if($A1:F31)=today() then to highlight the entire row, otherwise false. Not
sure how to write it up.

Thanks for you help,
Michael

 

[Dave Peterson]

By selecting a bunch of columns, excel will copy and adjust the formula for each
cell in your selection.

And since we put $A1 in the formula, the formula will always point to column A
and it'll adjust to point to the current row, too.

Try this in a test worksheet:

Select B1:F31 (stay away from A in the test)
type =$A1
but hit ctrl-enter instead of just enter.
Excel will fill the range with the adjusted formulas.

Now select a few cells and look at the formula (just to see what happened).

(In fact, put 1,2,3,... in A1:A31 and watch the results.)

 

[Mike]

Dave,
Works great!! Thanks for the explanation.

Michael

 

[David McRitchie]

Hi Mike,
Dave provided an excellent explanation on HOW Conditional
Formatting is applied. The following might be a little easier to remember.

Which cells can be get coloring is based on the selection.
The formula itself is based on the active cell. Each cell in
the selection will be tested and uses 1 of the 3 conditions.
per cell that you are allowed in C.F.

Therefore, you would make the column portion absolute,
when you want to color by rows.

I have a page on Conditional Formatting.
http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Dave,
Works great!! Thanks for the explanation.

Michael