Greetings,

I tried this yesterday and Bob Phillips gave me a link to a 30
condition Add In at:
http://xldynamic.com/source/xld.CFPlus.Download.html

It works but I can't get it to paste down and advance the cells as the
row numbers increase (like windows normally does.

Is anyone familiar with CF Plus and could explain how to paste down?

Or does anyone have a different solution as to how to get six
conditions on each cell in a range of 23 columns by 250 rows on 120
sheets (the cells in each row have different number, color & border
formats)? I almost forgot, Many of the rows already have data in
them so I have to be careful not to erase any.

Any help would be appreciated.

-Minitman

While I'm not familiar with that add-in, I'm pretty sure you'd have to run
the code again to make it work. Did you run the code after you added the
rows?
*******************
~Anne Troy

www.OfficeArticles.com


"Minitman" wrote in message
...
Greetings,

I tried this yesterday and Bob Phillips gave me a link to a 30
condition Add In at:
http://xldynamic.com/source/xld.CFPlus.Download.html

It works but I can't get it to paste down and advance the cells as the
row numbers increase (like windows normally does.

Is anyone familiar with CF Plus and could explain how to paste down?

Or does anyone have a different solution as to how to get six
conditions on each cell in a range of 23 columns by 250 rows on 120
sheets (the cells in each row have different number, color & border
formats)? I almost forgot, Many of the rows already have data in
them so I have to be careful not to erase any.

Any help would be appreciated.

-Minitman

Minitman,

It doesn't format the cell as built-in CF does, but uses some event code to
trap the value and apply a colour.

As such you don't paste the format down. What you should do is to select the
whole range at the start and apply your conditions to the whole range.

Perhaps it would be nice to have an option to extend the range.

--
HTH

Bob Phillips

"Minitman" wrote in message
...
Greetings,

I tried this yesterday and Bob Phillips gave me a link to a 30
condition Add In at:
http://xldynamic.com/source/xld.CFPlus.Download.html

It works but I can't get it to paste down and advance the cells as the
row numbers increase (like windows normally does.

Is anyone familiar with CF Plus and could explain how to paste down?

Or does anyone have a different solution as to how to get six
conditions on each cell in a range of 23 columns by 250 rows on 120
sheets (the cells in each row have different number, color & border
formats)? I almost forgot, Many of the rows already have data in
them so I have to be careful not to erase any.

Any help would be appreciated.

-Minitman

Hey Bob,

I was afraid of that. I tried to apply the conditions to a range, but
the Add In applied ALL properties to the entire range. since the
first cell in the range was a date, the percentage, number & currency
were all changed to date format. After that I realized that this Add
In was going to cause some problems. I tried several different ways
of pasting, but each one had it's own quirks. I seem to recall that
there is a way to do extra conditions using CASE. I think I'll pursue
that avenue unless you know of any other possibilities.

You were right though, this Add In does have a lot of potential and
would be worth paying for, when it is fully functional.

Thanks

-Minitman

On Mon, 18 Jul 2005 23:09:06 +0100, "Bob Phillips"
wrote:

Minitman,

It doesn't format the cell as built-in CF does, but uses some event code to
trap the value and apply a colour.

As such you don't paste the format down. What you should do is to select the
whole range at the start and apply your conditions to the whole range.

Perhaps it would be nice to have an option to extend the range.

Hey Bob,

I can get the sheet formatted now (Thanks for the tip), but how do I
get the other 120 sheets formatted with CF Plus?

Thanks.

-Minitman

On Mon, 18 Jul 2005 17:57:48 -0500, Minitman
wrote:

Hey Bob,

I was afraid of that. I tried to apply the conditions to a range, but
the Add In applied ALL properties to the entire range. since the
first cell in the range was a date, the percentage, number & currency
were all changed to date format. After that I realized that this Add
In was going to cause some problems. I tried several different ways
of pasting, but each one had it's own quirks. I seem to recall that
there is a way to do extra conditions using CASE. I think I'll pursue
that avenue unless you know of any other possibilities.

You were right though, this Add In does have a lot of potential and
would be worth paying for, when it is fully functional.

Thanks

-Minitman

On Mon, 18 Jul 2005 23:09:06 +0100, "Bob Phillips"
wrote:

Minitman,

It doesn't format the cell as built-in CF does, but uses some event code to
trap the value and apply a colour.

As such you don't paste the format down. What you should do is to select the
whole range at the start and apply your conditions to the whole range.

Perhaps it would be nice to have an option to extend the range.

Minitman,

I always like to think I can test for many conditions, as long as I can live
with only 3 possible outcomes, i.e., Conditions 1, 2, and 3. What kind of
conditions are you looking for and what kind of outcomes do you need? The CF
could be implanted with vba to preserve your other existing formats, data,
and handle the variances of color from row to row, plus, you can't copy and
paste in a CF, so manual editing can be a pain (unless someone can reveal
that secret to me), especially when Excel does you a favor and throws in the
extra quotation marks after you press OK, that you really didn't need.

For example, by typing this formula into Condition 1 of cell A1, you are in
effect testing for 8 conditions at once; 10, 13, 80, OK, RED, -4, 0 (zero),
but not empty. Entering any of the above values would turn the cell the color
of your choice, but the AND statement at the end keeps the cell from lighting
up if it contains nothing, which would otherwise be considered zero.

=OR(A1=10,A1=13,A180,A1="OK",A1="RED",A1=-4,AND(A1<"",A1=0))

As long as you can live with 3 possible appearance changes for any
particular cell, it should be do-able.

Roy

"Minitman" wrote:

Greetings,

I tried this yesterday and Bob Phillips gave me a link to a 30
condition Add In at:
http://xldynamic.com/source/xld.CFPlus.Download.html

It works but I can't get it to paste down and advance the cells as the
row numbers increase (like windows normally does.

Is anyone familiar with CF Plus and could explain how to paste down?

Or does anyone have a different solution as to how to get six
conditions on each cell in a range of 23 columns by 250 rows on 120
sheets (the cells in each row have different number, color & border
formats)? I almost forgot, Many of the rows already have data in
them so I have to be careful not to erase any.

Any help would be appreciated.

-Minitman

Hey Roy,

Thanks for the reply.

I have already maxed out the CF in Excel. Here are the conditions in
the CF:

1) =AND($A2="",$B2="")
True gives light tan with light tan font, Or cell is blank.
False gives default white. Or this row has either a service date(A2),
a deposit date(B2) or both.

2)=(ROUND(SUMIF($X$2:$X$250,$X2,$E$2:$E$250),2)-ROUND(SUMIF($X$2:$X$250,$X2,$F$2:$F$250),2))0
True gives pink. Or deposit tickets missing.
False gives default white.

3)=(ROUND(SUMIF($X$2:$X$250,$X2,$F$2:$F$250),2)-ROUND(SUMIF($X$2:$X$250,$X2,$E$2:$E$250),2))0
True gives light blue. Or missing bank statement
False gives default white. Or no missing bank statement or deposit
ticket.

I still need to see if the entry is a non-bank item and if it is a
particular customer or not. Yellow means entry is this customer & a
non-bank item. While light purple means that this entry is only a
non-bank item. This is where I need the extra conditions.

I still need to find out how to get these formats onto the other 119
sheets, any ideas?

Any help is appreciated.

-Minitman



On Mon, 18 Jul 2005 17:29:03 -0700, Roy
wrote:

Minitman,

I always like to think I can test for many conditions, as long as I can live
with only 3 possible outcomes, i.e., Conditions 1, 2, and 3. What kind of
conditions are you looking for and what kind of outcomes do you need? The CF
could be implanted with vba to preserve your other existing formats, data,
and handle the variances of color from row to row, plus, you can't copy and
paste in a CF, so manual editing can be a pain (unless someone can reveal
that secret to me), especially when Excel does you a favor and throws in the
extra quotation marks after you press OK, that you really didn't need.

For example, by typing this formula into Condition 1 of cell A1, you are in
effect testing for 8 conditions at once; 10, 13, 80, OK, RED, -4, 0 (zero),
but not empty. Entering any of the above values would turn the cell the color
of your choice, but the AND statement at the end keeps the cell from lighting
up if it contains nothing, which would otherwise be considered zero.

=OR(A1=10,A1=13,A180,A1="OK",A1="RED",A1=-4,AND(A1<"",A1=0))

As long as you can live with 3 possible appearance changes for any
particular cell, it should be do-able.

Roy

"Minitman" wrote:

Greetings,

I tried this yesterday and Bob Phillips gave me a link to a 30
condition Add In at:
http://xldynamic.com/source/xld.CFPlus.Download.html

It works but I can't get it to paste down and advance the cells as the
row numbers increase (like windows normally does.

Is anyone familiar with CF Plus and could explain how to paste down?

Or does anyone have a different solution as to how to get six
conditions on each cell in a range of 23 columns by 250 rows on 120
sheets (the cells in each row have different number, color & border
formats)? I almost forgot, Many of the rows already have data in
them so I have to be careful not to erase any.

Any help would be appreciated.

-Minitman

There is no cross-sheet setting I am afraid, it wasn't thought of as a
necessary requirement. May be that would be another useful plug-in to
provide that as a function, I think it would be quite straight-forward if it
were the same range and the same format on each sheet. You would have to do
each individually

When you say that you have the sheet formatted okay now, does that mean that
you have overcome those problems in the last post? One big advantage is that
you can apply more formats than built-in CF. And there is the 'Highlight
Range' function, which I like a lot.

--
HTH

Bob Phillips

"Minitman" wrote in message
...
Hey Bob,

I can get the sheet formatted now (Thanks for the tip), but how do I
get the other 120 sheets formatted with CF Plus?

Thanks.

-Minitman

On Mon, 18 Jul 2005 17:57:48 -0500, Minitman
wrote:

Hey Bob,

I was afraid of that. I tried to apply the conditions to a range, but
the Add In applied ALL properties to the entire range. since the
first cell in the range was a date, the percentage, number & currency
were all changed to date format. After that I realized that this Add
In was going to cause some problems. I tried several different ways
of pasting, but each one had it's own quirks. I seem to recall that
there is a way to do extra conditions using CASE. I think I'll pursue
that avenue unless you know of any other possibilities.

You were right though, this Add In does have a lot of potential and
would be worth paying for, when it is fully functional.

Thanks

-Minitman

On Mon, 18 Jul 2005 23:09:06 +0100, "Bob Phillips"
wrote:

Minitman,

It doesn't format the cell as built-in CF does, but uses some event code
to
trap the value and apply a colour.

As such you don't paste the format down. What you should do is to select
the
whole range at the start and apply your conditions to the whole range.

Perhaps it would be nice to have an option to extend the range.

Hey Bob,

It was indeed the "Highlight Range" that helped. I even tried to
highlight the entire area that I was trying to format (A2:W250), but
ran into the other limitation. The way the Add In is setup, it takes
ALL of the formatting (numbers, alignment, font. borders, patterns and
protection) the ones you change and the ones you don't change along
with the conditions you set and puts them into the range highlighted.
Which works if you highlight a range with common properties , such as
a column. I did not work as well where I had some columns with
currency, some with percent, some with date formatting, whichever was
in the first cell was copied to all in the range. I even tried
reformatting the individual columns and it seemed to work, until I
left that sheet and returned. The reformatted columns where turned
back into the CF Plus formats. But if I limit the highlighted range
to a column at a time, then it works. And you are right about the
ability to paste onto a different sheet, the error message says that
it won't do it and Paste Special is grayed out!!!!

Unfortunately, for this project, I must look elsewhere for a solution.
I know there is one out there. There is a post offering another beta
extended CF solution that appeared this morning, that I think I will
give a try.

Thanks for the help, at least I got to see how the CF Plus works and
it's limitations. I learned something new, and that is always good.

-Minitman




On Tue, 19 Jul 2005 08:46:52 +0100, "Bob Phillips"
wrote:

There is no cross-sheet setting I am afraid, it wasn't thought of as a
necessary requirement. May be that would be another useful plug-in to
provide that as a function, I think it would be quite straight-forward if it
were the same range and the same format on each sheet. You would have to do
each individually

When you say that you have the sheet formatted okay now, does that mean that
you have overcome those problems in the last post? One big advantage is that
you can apply more formats than built-in CF. And there is the 'Highlight
Range' function, which I like a lot.