"Fill" Conditional Formatting?

  • Thread starter Thread starter Deanna
  • Start date Start date
D

Deanna

Once I have my conditional formatting set for an entire row, is there any way
to fill down the rows on the rest of the spreadsheet like you would a formula
typed directly into a cell? I've tried filling down, and I think each row is
refencing the cells in the original row instead of the appropriate cell in
it's own row. (Excel 2003)

Is is possible, or do I have to set the conditional formulas for each row
individually?

Thanks...
 
You can copy down ... just like a regular formula ... and just like a
regular formula, you'll need to use relative references in place of absolute
($) references where necessary.

You might post your formulas if you still have a problem.
 
RagDyer said:
You can copy down ... just like a regular formula ... and just like a
regular formula, you'll need to use relative references in place of
absolute ($) references where necessary.
Click to expand...

You can also use the format painter by clicking at the end of your set row,
and then dragging it down the rows you want to format. Try one row first to
ensure you have the correct relative vs. absolute references in the
conditional formatting.

V
 
Thanks to you both, I removed the $ where appropriate, and it's working, for
the most part.

I was able to fill down a single cell through the remainder of the column
and it worked beautifully. Where I'm still having trouble is filling down
for an entire row.

I highlighted the cells in the row I wanted the conditional formatting to
apply to, and here are the two conditions I wrote:

Formula Is =AND(ISBLANK(H12),$G$2>G12)
I do want the absolute reference to G2, as that is a date entry that
will change to affect the results I'm looking for. A TRUE statement will
highlight the entire row yellow to indicate it's an overdue item as there is
no completion date entered (H12 is blank) and the target due date (G12) is
past today's date ($G$2).

Formula Is =ISTEXT(H12)
I want the row to highlight green to indicate there is a completion
date entered in H12, and it is now a closed item.

What isn't working is that the entire row isn't highlighting...two cells are
staying clear when the item is overdue, and one cell is staying clear when
the item is completed.

Also, when I try to fill down the formatting, it's filling down the text of
the cells...should I use format painter in this instance, as I'm changing a
spreadsheet that's already in common use and can't (and don't want to!!!)
necessarily start from scratch on the data entry.
 
Don't forget what I originally stated in my first post,
these CF formulas are like regular formulas (except for circular
references),
so you *will need* absolutes in some situations.

When you attempt to copy the CF across columns, along a row, you'll need the
column references to be absolute.

ALSO, your use of Istext() to evaluate a date entry is not the wisest
function to use.
If dates are entered as 7/28/09, XL treats it as a *number*, and will return
FALSE, therefore not invoking the CF for those cells.
If there is a possibility that the date *might* be entered as text
(July-22-),
then this formula should cover both cases:

=$H12<>""

AND, for your first condition, use:

=AND(ISBLANK($H12),$G$2>$G12)

Finally, if you already have data in those cells you're looking to use CF
on,
*DO* use the "Format Painter".

As a hint, *double* clicking on the "Format Painter" icon will keep it
active through multiple mouse clicks.
Don't forget to hit <Esc> to turn it off, or you could make a real mess of
your sheet!
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Thanks to you both, I removed the $ where appropriate, and it's working, for
the most part.

I was able to fill down a single cell through the remainder of the column
and it worked beautifully. Where I'm still having trouble is filling down
for an entire row.

I highlighted the cells in the row I wanted the conditional formatting to
apply to, and here are the two conditions I wrote:

Formula Is =AND(ISBLANK(H12),$G$2>G12)
I do want the absolute reference to G2, as that is a date entry that
will change to affect the results I'm looking for. A TRUE statement will
highlight the entire row yellow to indicate it's an overdue item as there is
no completion date entered (H12 is blank) and the target due date (G12) is
past today's date ($G$2).

Formula Is =ISTEXT(H12)
I want the row to highlight green to indicate there is a completion
date entered in H12, and it is now a closed item.

What isn't working is that the entire row isn't highlighting...two cells are
staying clear when the item is overdue, and one cell is staying clear when
the item is completed.

Also, when I try to fill down the formatting, it's filling down the text of
the cells...should I use format painter in this instance, as I'm changing a
spreadsheet that's already in common use and can't (and don't want to!!!)
necessarily start from scratch on the data entry.
 
I believe there is a glitch that you can not fill conditional formatting to a separate block of cells. Picture a square comprised of four cells, and the value in the bottom left cell is what will determine the colour of these four cells. If you want to copy this conditional format to create a neighbouring square (also comprised of four cells with the bottom left determing the format), it does not work. The formulas will appear to have copied correctly but the result is not correct. It will only correctly colour the top left square *even though* the cell range correctly indicates all for squares when you look at the Rules Manager. Try it and tell me what you think? If you have a solution, I'd love to hear it because as it is I am having to manual enter about 500 squares!

dm
 
Back
Top