How do I get a line accross the sheet when a certain value is reached
Posted by Chris Brown on January 07, 2002 5:31 AM
I have a budgeting spreadsheet showing what projects I plan to do next year. It has a cumulative spend column. I want to automatically insert a line accross the spreadsheet at the first line which exceeds the budget. I want it to be auto because both the budget and individual project costs can change any time. Does anyone know how to get this? I just can't figure it...
Chris
Posted by Big Bob on January 07, 2002 7:09 AM
This sounds like a situation made for conditional formatting
Posted by Tom Urtis on January 07, 2002 7:49 AM
Is this what you want?
Right click on your sheet tab, left click on View Code, and paste this in:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If Target.Cells.Value > Range("D1").Value Then
With Target.Cells.EntireRow.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
End Sub
Assumes your column of interest is D, and the cell containing the "can't go over" amount is D1. If not, please modify.
Any help?
Tom Urtis
Posted by Chris Brown on January 08, 2002 3:48 AM
Re: Is this what you want?
Brilliant! Thanks Tom
Posted by Chris Brown on January 08, 2002 11:46 AM
Re: Is this what you want?
Hang on Tom, I jumped the gun. I didn't get it to work.
I put the formula in, but nothing happened. I tried creating
a spreadsheet that uses D1/col D as your example to be sure
I didn't mess it up. Still didn't work.
Do I have to activate/run this formula or something?
Chris
