format border based upon if, but what about loop? 1

[shelron]

I am trying to create a moving funding line, if the amount in column N6:N387 is greater than $737,844,644 I want the row to be underlined.

First I am getting a mismatch error, I think due to the currency, secondly, how do I get this to loop down the column and keep evaluating till the condition is true?? Not sure of the syntax. (I guess I will have to add some general formatting so that multiple rows do not become underlined as the funding line moves.)

If I can get this correct, I will tie the code to a cmd button.

Excel 2002,

thanks for any help, Ron








Sub exampleformat()
'create line on worksheet at funding line > $737,844,644

If ("N6&quot > 737844644 Then

Range("B6:N6&quot.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With


Else: End If





End Sub

 

[DaleWatson123321]

Hi shelron,

First, I assume from your description, that your value of 737,844,644 can be anywhere in column N, between rows 6 and 387. And wherever the amount exceeds that number, you want the entire row underlined.

If this is correct, you could use Conditional Formatting. I just confirmed that the following steps work.

1) Off to the side (out of the way), enter your 737844644 value into a cell, and assign the name "num" to that cell.

2) In cell A6, use Conditional Formatting. From the menu: Format - Conditional Formatting (or <Alt> O D)

3) Choose &quot;Formula Is&quot;, and then enter this formula: =$N6>num

4) Click &quot;Format&quot;, click &quot;Borders&quot;, and enter an a line a the bottom of the box. Click OK twice to finish.

5) Because you don't want to overwrite any data you will likely have in the other columns, you'll need to copy this cell, and then use &quot;Paste Special&quot; - &quot;Formats&quot;. Therefore, copy cell A6. Then highlight the entire range where you want this formatting to apply - possibly from A6:N387. Then use: &quot;Edit - Paste Special - Formats&quot;.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[shelron]

A

http://www geeeeeeeeezeeeeeeeeee

Dale, I was trying to make it hard by using code, Did you have to go and make it easy???

Your solution is easy, logical and simple, thanks for the umteenth time.

preciate it, Ron

 

[shelron]

hmmmmmmmm, I didn't name the range, just used an absolute reference,


but when i copy and paste, the formula pasted doesn't become a relative reference for the row,, just stays &quot;N6&quot; even though it started as &quot;$N6&quot;

if I can get over this issue, it should work like a charm,

ron

 

[DaleWatson123321]

Hi Ron,

You'll need to edit cell A6, and remove the quotation marks.

If you didn't name the cell containing your maximum number, then your formula should look like this...

=$N6>$R$2

where $R$2 is the cell containing your maximum number.

A further tip... When you enter the formula box, hit the <F2> key - which is the &quot;Edit&quot; key. This will help you in being able to edit the formula WITHOUT having the formula reference cell addresses as you move the cursor.

Hope this helps - Please advise. ...and THANKS for the STAR.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[shelron]

alrighty then, I had =if&quot;$n6>sum&quot;

hate when that happens, forgot the wizard was already writing the if condition,

thanks for your help,

ron