I set a conditional format in a cell (A1) using a formula (=B1="") to make
the cell red if cell B1 is blank. I then used the Format Painter to copy
this conditional format to other cells - say A2 thru A100. If I look at the
conditional format in cell A47, why does it tell me that this conditional
format formula is =B2="", and that it "Applies to $A$2:$A$100"?

I liking Excel 2007 less and less each day.

--
Bill @ UAMS

Hello Bill,

Pl. read this with an open mind...

What you have really done is use the formula =B1="" and then painted it to
apply to $A$2:$A$100, right?

That is exactly what Excel 2007 is telling you ... that the formula is
(=B2="") and the range it is applied to is $A$2:$A$100. You know and I know
that Excel applies the relative formula by changing the row number
appropriately... Won't you get confused if it showed the formula (=B47=""
)for the current row and still showed the range as $A$2:$A$100?

btw can you think of any other way to convey the information if you want to
show the range the formula is applied to?

Also how is it worse than Excel 2003 where you had to go each cell to see
what the conditional formatting was for each cell (though it showed you the
current row reference)?

If you go back to A1 it will show you =B1="" and say it applies to the
range A1:A1

Pl. don't shoot me if you don't like the message :-)

"BillCPA" wrote:

I set a conditional format in a cell (A1) using a formula (=B1="") to make
the cell red if cell B1 is blank. I then used the Format Painter to copy
this conditional format to other cells - say A2 thru A100. If I look at the
conditional format in cell A47, why does it tell me that this conditional
format formula is =B2="", and that it "Applies to $A$2:$A$100"?

I liking Excel 2007 less and less each day.

--
Bill @ UAMS

So are you telling me if I want (=B47="") to apply just to A47, I have to
manually enter it as a conditional format in A47? And if I want (=B46="") to
apply just to A46, I have to manually enter it as a conditional format in
A46? And if I want (=B72="") to apply just to A72, I have to manually enter
it as a conditional format in A72? There is no copying of conditional
formats with format painter?

In Excel 2003, Format Painting didn't carry all this excess baggage - if you
did a format paint, it simply painted the format of the source cell and
adjusted for row numbers and column numbers appropriately.

And while we are on the subject, if I make a change to the conditional
format in A1, and then do a format paint to A2 through A100, why does it
leave the original conditional format in the destination cells? Do I have to
delete a conditional format and add a new one every time I want to change
something in the formula?

None of this makes sense to me. If I create a conditional format in a cell
and copy it to 1000 other cells, then make three changes to the conditional
format in original cell and recopy it to the same 1000 cells, those 1000
cells now each have four conditional formats. No wonder 2007 runs slower
than a turtle in quicksand.

None of this is helping my productivity.

--
Bill @ UAMS


"Sheeloo" wrote:

Hello Bill,

Pl. read this with an open mind...

What you have really done is use the formula =B1="" and then painted it to
apply to $A$2:$A$100, right?

That is exactly what Excel 2007 is telling you ... that the formula is
(=B2="") and the range it is applied to is $A$2:$A$100. You know and I know
that Excel applies the relative formula by changing the row number
appropriately... Won't you get confused if it showed the formula (=B47=""
)for the current row and still showed the range as $A$2:$A$100?

btw can you think of any other way to convey the information if you want to
show the range the formula is applied to?

Also how is it worse than Excel 2003 where you had to go each cell to see
what the conditional formatting was for each cell (though it showed you the
current row reference)?

If you go back to A1 it will show you =B1="" and say it applies to the
range A1:A1

Pl. don't shoot me if you don't like the message :-)

"BillCPA" wrote:

I set a conditional format in a cell (A1) using a formula (=B1="") to make
the cell red if cell B1 is blank. I then used the Format Painter to copy
this conditional format to other cells - say A2 thru A100. If I look at the
conditional format in cell A47, why does it tell me that this conditional
format formula is =B2="", and that it "Applies to $A$2:$A$100"?

I liking Excel 2007 less and less each day.

--
Bill @ UAMS

What I was trying to say is that the behavior is same just the way it is
shown is different...

When you create a condition in A1 and paint it to A2:A100, it gets applied
to that range after adjusting relative references (i.e. adjusted for row
numbers and column numbers appropriate) ...

In your case for
A47 condition would be =B47=""
A72 condition would be =B72=""

just as in Excel 2003...

Also if you want to change any formatting already applied to any range...
you can just change it in the first cell... and remember to update the
APPLIES TO range, if required...

btw did you notice that Excel 2007 is not limited to 3 conditions? You can
also have ICON SETs etc.?

"BillCPA" wrote:

So are you telling me if I want (=B47="") to apply just to A47, I have to
manually enter it as a conditional format in A47? And if I want (=B46="") to
apply just to A46, I have to manually enter it as a conditional format in
A46? And if I want (=B72="") to apply just to A72, I have to manually enter
it as a conditional format in A72? There is no copying of conditional
formats with format painter?

In Excel 2003, Format Painting didn't carry all this excess baggage - if you
did a format paint, it simply painted the format of the source cell and
adjusted for row numbers and column numbers appropriately.

And while we are on the subject, if I make a change to the conditional
format in A1, and then do a format paint to A2 through A100, why does it
leave the original conditional format in the destination cells? Do I have to
delete a conditional format and add a new one every time I want to change
something in the formula?

None of this makes sense to me. If I create a conditional format in a cell
and copy it to 1000 other cells, then make three changes to the conditional
format in original cell and recopy it to the same 1000 cells, those 1000
cells now each have four conditional formats. No wonder 2007 runs slower
than a turtle in quicksand.

None of this is helping my productivity.

--
Bill @ UAMS


"Sheeloo" wrote:

Hello Bill,

Pl. read this with an open mind...

What you have really done is use the formula =B1="" and then painted it to
apply to $A$2:$A$100, right?

That is exactly what Excel 2007 is telling you ... that the formula is
(=B2="") and the range it is applied to is $A$2:$A$100. You know and I know
that Excel applies the relative formula by changing the row number
appropriately... Won't you get confused if it showed the formula (=B47=""
)for the current row and still showed the range as $A$2:$A$100?

btw can you think of any other way to convey the information if you want to
show the range the formula is applied to?

Also how is it worse than Excel 2003 where you had to go each cell to see
what the conditional formatting was for each cell (though it showed you the
current row reference)?

If you go back to A1 it will show you =B1="" and say it applies to the
range A1:A1

Pl. don't shoot me if you don't like the message :-)

"BillCPA" wrote:

I set a conditional format in a cell (A1) using a formula (=B1="") to make
the cell red if cell B1 is blank. I then used the Format Painter to copy
this conditional format to other cells - say A2 thru A100. If I look at the
conditional format in cell A47, why does it tell me that this conditional
format formula is =B2="", and that it "Applies to $A$2:$A$100"?

I liking Excel 2007 less and less each day.

--
Bill @ UAMS

I do not agree that the behavior is the same, but the only way I can argue
that is going to be my specific example.

I have a spreadsheet with about 12,000 rows and fourteen columns. In about
half of those columns I have fairly complicated formulas that will apply to
all rows, and I want conditional formatting in most cells. In row 20 I
entered the formulas and conditional formatting, which I then copied down a
few hundred rows to see how they work with different data. When I found
something that needed changing, I went back to row 20, made changes, and
copied down again. After I was satisfied that section worked the way I
needed, I copied down a couple thousand rows, found things that needed
changing, made changes in row 20, and copied down again, first a few hundred
to make sure nothing changed there, then a couple thousand again.

Each copy added a new conditional format to all the cells below row 20.
Most are duplicates of previous ones, but if I changed the conditional format
in a cell in row 20, then that was considered an additional conditional
format in the cells below, not a replacement. So the behavior is not the
same as with Excel 2003 - I now have conflicting conditional formats in the
cells below row 20. And, the conditional formats get applied in the order
they are listed, and the changed (or new) formats from row 20 are added at
the bottom of the list, so they may not be the ones executed. When I found
that to be the case, my only recourse seemed to be to totally clear out all
the rows below row 20, and start copying again.

You say if I want to change any formatting already applied to any range, I
can just change it in the first cell. Well, where is the first cell? If I
create a spreadsheet to handle one weeks' worth of data, I'll know where it
is. If I select a row and copy it down to increase the size for a second
week of data, I'll probably know where it is also. But after I've increased
the size several times for another week's worth of data, or possibly ten
days' worth or a month's worth or a quarter's worth of data, where are all of
the 'first rows' of data that now apply to different ranges.

I have a bank reconciliation process that I have run for years using Excel
2003. It has a couple hundred macros in it, some of which insert (and copies
to similar cells) conditional formatting to help show me where certain data
needs to be looked at. The colorful display I got the first time I ran it
under Excel 2007 was amazing, but it was basically irrelevant to what I
needed to know. Plus, that particular process took about 10-12 hours to run
instead of the normal hour or so. The time I had to spend getting it to show
me what I needed was absurd, and it still takes 10-12 hours to run.

Now, if there is a different way I need to go about creating and testing a
spreadsheet in Excel 2007, I'll certainly consider it. If there is a way to
avoid what I'm getting, I'll be glad to hear it. But telling me the behavior
is the same - not in my spreadsheets. Is Excel 2007 a wonderful tool that
increases productivity and makes me a better employee - not in my book.

--
Bill @ UAMS


"Sheeloo" wrote:

What I was trying to say is that the behavior is same just the way it is
shown is different...

When you create a condition in A1 and paint it to A2:A100, it gets applied
to that range after adjusting relative references (i.e. adjusted for row
numbers and column numbers appropriate) ...

In your case for
A47 condition would be =B47=""
A72 condition would be =B72=""

just as in Excel 2003...

Also if you want to change any formatting already applied to any range...
you can just change it in the first cell... and remember to update the
APPLIES TO range, if required...

btw did you notice that Excel 2007 is not limited to 3 conditions? You can
also have ICON SETs etc.?

"BillCPA" wrote:

So are you telling me if I want (=B47="") to apply just to A47, I have to
manually enter it as a conditional format in A47? And if I want (=B46="") to
apply just to A46, I have to manually enter it as a conditional format in
A46? And if I want (=B72="") to apply just to A72, I have to manually enter
it as a conditional format in A72? There is no copying of conditional
formats with format painter?

In Excel 2003, Format Painting didn't carry all this excess baggage - if you
did a format paint, it simply painted the format of the source cell and
adjusted for row numbers and column numbers appropriately.

And while we are on the subject, if I make a change to the conditional
format in A1, and then do a format paint to A2 through A100, why does it
leave the original conditional format in the destination cells? Do I have to
delete a conditional format and add a new one every time I want to change
something in the formula?

None of this makes sense to me. If I create a conditional format in a cell
and copy it to 1000 other cells, then make three changes to the conditional
format in original cell and recopy it to the same 1000 cells, those 1000
cells now each have four conditional formats. No wonder 2007 runs slower
than a turtle in quicksand.

None of this is helping my productivity.

--
Bill @ UAMS


"Sheeloo" wrote:

Hello Bill,

Pl. read this with an open mind...

What you have really done is use the formula =B1="" and then painted it to
apply to $A$2:$A$100, right?

That is exactly what Excel 2007 is telling you ... that the formula is
(=B2="") and the range it is applied to is $A$2:$A$100. You know and I know
that Excel applies the relative formula by changing the row number
appropriately... Won't you get confused if it showed the formula (=B47=""
)for the current row and still showed the range as $A$2:$A$100?

btw can you think of any other way to convey the information if you want to
show the range the formula is applied to?

Also how is it worse than Excel 2003 where you had to go each cell to see
what the conditional formatting was for each cell (though it showed you the
current row reference)?

If you go back to A1 it will show you =B1="" and say it applies to the
range A1:A1

Pl. don't shoot me if you don't like the message :-)

"BillCPA" wrote:

I set a conditional format in a cell (A1) using a formula (=B1="") to make
the cell red if cell B1 is blank. I then used the Format Painter to copy
this conditional format to other cells - say A2 thru A100. If I look at the
conditional format in cell A47, why does it tell me that this conditional
format formula is =B2="", and that it "Applies to $A$2:$A$100"?

I liking Excel 2007 less and less each day.

--
Bill @ UAMS