Neeb adjustment to VB code to capture a range.

[davidhall80]

The code below puts a green border around the cell that is beneath 10 in my chosen range, however I wish to add the border to the row of information instead of just the cell. My columns of data are from columns E to M, but the criteria for whether or not the data gets a green border is in column D....so lets say D15 is less than 10, I would want a border to go around E15:M15. Thanks in advance. Also If there is a better way to write this, please inform me...Thanks again



Sub Test()
For Each c In Range("D2:D350")
If c < 10 Then
c.select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End If
Next c
End Sub

 

[wisewood]

I think...

If Cells(4, 15).Value < 15 Then
     Range("E15:M15").Select
With Selection.Borders(xlEdgeLeft) 
.LineStyle = xlContinuous 
.Weight = xlThin 
.ColorIndex = 4 
End With 
With Selection.Borders(xlEdgeTop) 
.LineStyle = xlContinuous 
.Weight = xlThin 
.ColorIndex = 4 
End With 
With Selection.Borders(xlEdgeBottom) 
.LineStyle = xlContinuous 
.Weight = xlThin 
.ColorIndex = 4 
End With 
With Selection.Borders(xlEdgeRight) 
.LineStyle = xlContinuous 
.Weight = xlThin 
.ColorIndex = 4 
End With 
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone 
End If

 

[Lewiy]

I think you just need this:

Sub Test()
Dim c As Long
For c = 2 To 350
    If Cells(c, 4) < 10 Then
        Range("E" & c).BorderAround xlContinuous, xlThin, 4
    End If
Next c
End Sub

 

[davidhall80]

Wisewood...I was just using one row as an example..I may have confused you. Lewiy's code works for what I need, but thanks anyhow....Lewiy, can you change this line of code "For c = 2 To 350"...Reason being, I only want to capture the rows of data that I have for a given week...One week it might be 350, the next week, it might be 200...it that possible??

Dim c As Long
For c = 2 To 350
If Cells(c, 4) < 10 Then
Range("C" & c & ":" & "M" & c).BorderAround xlContinuous, xlThin, 4
End If
Next c

 

[Norie]

Couldn't you just use conditional formatting instead of code?

 

[davidhall80]

I could, but I need other cells in the row besides the one under 10 to be formatted. I need the whole row...or at least part of it. If I was just doing the one cell, I conditionally format the entire column....

 

[Norie]

David

And why couldn't you do that using conditional formatting?

I just did.

Select E2:M350, goto Format>Conditional formatting..., select formula is, enter this

=$D2<10

and then format as required

 

[davidhall80]

There's no formula on the sheet that says =if x is less than 10...that's in the VB. I tried the above...didn't work....Lewiy's code works fine...I just need the "For c = 2 To 350" to be variable from week to week. If I have 200 hundered rows next week, I need it to read "For c = 2 To 200"

 

[Norie]

David

What exactly did you try?

Like I said the conditional formatting worked for me.

Oh, and it didn't involve any formulas in the worksheet.

I was referring to the option to use a formula as the criteria in conditional formatting which is available from the dropdown.

 

[davidhall80]

Can you record it and stick it on the thread so I can see what it looks like. I'll try and fit it into the current Macro I'm using....Thanks Norie