Conditional Formatting Whole Row

  • Thread starter Thread starter Mike
  • Start date Start date
M

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
 
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()
 
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
 
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.)
 
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


Mike said:
Dave,
Works great!! Thanks for the explanation.

Michael
Click to expand...
 
Back
Top