Neeb adjustment to VB code to capture a range.

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
I think...

Code:
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
I think you just need this:
Code:
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

Well-known Member
Joined
Jul 8, 2006
Messages
663
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 8, 2006
Messages
663
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 8, 2006
Messages
663
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top