Tom Urtis

Chris,

This does work; I just pasted this code off your post and tested it again now and it is fine.

As I recall, you asked for an ability to have a line be placed on an entire row directly below a number that gets entered, which is greater than a budgeted number located in some cell elsewhere.

First, be sure you right click on your sheet tab, left click on View Code, and paste in the code.

This code serves as an example that presumably you would need to modify for your situation, because I can't see your worksheet. In my example, if you have a ceiling budgeted number in cell D1, then from D2 on down, any number greater than 50, once entered in column D, will cause a line to be drawn directly below it (actually it is a bottom-edge cell border, but it achieves the effect you asked for).

Please try it again and let me know if you still have difficulty.

Tom Urtis

Addendum...

In my above response I wrote "any number greater than 50". It's early in Calif; when I tested this now, 50 was what I placed in D1 to test it again. Any number in D1 will do, and any number higher than that in column D will invoke the row line.

T.U. ,

: Tom, : you sent me some code a couple of days ago

Another way?

Tom- I missed the original question, but could this be done through conditional formatting?

ie- Select the whole sheet, and use a conditional format formula of =$D1>50 (or whatever) and format to place a bottom border line. Apologies if I've got the wrong end of the stick. :

Re: Another way?

Hi Mudface,

FYI, here's the related url:
13429.html

Your suggestion is possible but the $50 was only an example in my post this morning, based on another test I did to verify that this code works.

I felt that a WorksheetChange event might be the easiest to implement and use, without explaining conditional formatting formulas or copying the conditional format to whatever cells might be affected in his spreadsheet. The change code is also quite flexible, needing minor modifications based on what cell Chris is evaluating.

But I'm always open for ideas, so if you see a better solution after you read his original post, by all means I'd be interested to hear it.

Thanks.

Tom Urtis

Tom- I missed the original question, but could this be done through conditional formatting? ie- Select the whole sheet, and use a conditional format formula of =$D1>50 (or whatever) and format to place a bottom border line. Apologies if I've got the wrong end of the stick. : In my above response I wrote "any number greater than 50". It's early in Calif; when I tested this now, 50 was what I placed in D1 to test it again. Any number in D1 will do, and any number higher than that in column D will invoke the row line. : T.U.

Re: Another way?

Thanks, Tom. I agree without a few more details the code is a much better solution.

My thoughts were, based on the budget being entered in any cell and Named "Budget", say and cumulative totals being in the D column, that the following would suffice: -

a) Select entire sheet by pressing top left hand corner.

b) Select Format- Conditional Formatting and Formula is

c) Enter the formula as "=$D1>Budget"

d) Choose the single under border from the border tab.

This would then give a solid under border across the entire row whenever a value in the D column exceeds the budget. This of course pre-supposes that the total is always going to be in the one column and would only work if up to 3 columns were to be used.

Maybe, if I haven't muddied the waters completely :), Chris could say exactly where his data is going to be?

Hi Mudface, FYI, here's the related url: 13429.html Your suggestion is possible but the $50 was only an example in my post this morning, based on another test I did to verify that this code works. I felt that a WorksheetChange event might be the easiest to implement and use, without explaining conditional formatting formulas or copying the conditional format to whatever cells might be affected in his spreadsheet. The change code is also quite flexible, needing minor modifications based on what cell Chris is evaluating. But I'm always open for ideas, so if you see a better solution after you read his original post, by all means I'd be interested to hear it. Thanks.

Re: Another way?

Tom/Mudface, first thanks for your interest. I'm pretty sure you both grasped the problem correctly.

First, Tom. I pasted your code into a spreadsheet I made for the purpose that uses cell d1 and Col d, to make sure I hadn't mucked up the edit. On my machine, it only draws the line if the value is in D2. Any lower and nothing happens. Also, if the value in D2 then falls below D1, the line doesn't dissapear.

Mudface: I had already worked out a way to do this with a formula in conditional formatting, but I could only get a thin line with this method. Thick lines are not on the pick list for conditional formats.....unless you know better

regards

Chris

Re: Another way?

Chris,

I would really like to see why you are getting the inconsistent results you getting...the code works for me anytime I enter a number in column D that is a higher figure than what resides in D1. Please email me your file, just as you tested it today, to
TomUrtis@attbi.com

Also, I see your point about the line not disappearing after it first gets made, but I imagined that cumulative numbers over time (as dollars spent for instance) would never decrease, since money spent is money forever committed, hence the totals are always rising.

Anyway, I'll be able to suggest something more concrete once I see firsthand what you are doing.

Tom Urtis

Tom/Mudface, first thanks for your interest. I'm pretty sure you both grasped the problem correctly. First, Tom. I pasted your code into a spreadsheet I made for the purpose that uses cell d1 and Col d, to make sure I hadn't mucked up the edit. On my machine, it only draws the line if the value is in D2. Any lower and nothing happens. Also, if the value in D2 then falls below D1, the line doesn't dissapear. Mudface: I had already worked out a way to do this with a formula in conditional formatting, but I could only get a thin line with this method. Thick lines are not on the pick list for conditional formats.....unless you know better regards