Border VBA

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
GM All

I am writing this code in VBA to apply a border to a cell if the cell value is "John". Is this the most efficient way to accomplish this task.

Below is the code

Sub add_border()


For x = 1 To 100

If Cells(x, 2) = "John" Then

Cells(x, 2).Borders.Weight = 2.5
Cells(x, 2).Borders.Color = RGB(255, 0, 0)

End If


Next x


End Sub

Thank You...
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
for just John use CF Cell Value = "John" and add a border
 
Upvote 0
I agree with Mole999 that using conditional formatting is a straightforward option but if you do want a VBA solution then the code below should be faster than the code in the first post.
Code:
Sub FindJohn()

    Dim fnd As String, Found1 As String
    Dim fCell As Range, Rng As Range
    Dim myRng As Range, lCell As Range

    fnd = "John"

    Set myRng = ActiveSheet.Columns(2)
    Set lCell = myRng.Cells(myRng.Cells.Count)
    Set fCell = myRng.Find(What:=fnd, After:=lCell)

    If Not fCell Is Nothing Then
        Found1 = fCell.Address
    Else
        GoTo NothingFound
    End If

    Set Rng = fCell
    Do Until fCell Is Nothing
        Set fCell = myRng.FindNext(After:=fCell)

        Set Rng = Union(Rng, fCell)
        If fCell.Address = Found1 Then Exit Do
    Loop

    With Rng
        .Borders.Weight = 2.5
        .Borders.Color = RGB(255, 0, 0)
    End With

    Exit Sub
NothingFound:
    MsgBox "No values were found in Column B"

End Sub
 
Upvote 0
I agree with Mole999 that using conditional formatting is a straightforward option but if you do want a VBA solution then the code below should be faster than the code in the first post.
Code:
Sub FindJohn()

    Dim fnd As String, Found1 As String
    Dim fCell As Range, Rng As Range
    Dim myRng As Range, lCell As Range

    fnd = "John"

    Set myRng = ActiveSheet.Columns(2)
    Set lCell = myRng.Cells(myRng.Cells.Count)
    Set fCell = myRng.Find(What:=fnd, After:=lCell)

    If Not fCell Is Nothing Then
        Found1 = fCell.Address
    Else
        GoTo NothingFound
    End If

    Set Rng = fCell
    Do Until fCell Is Nothing
        Set fCell = myRng.FindNext(After:=fCell)

        Set Rng = Union(Rng, fCell)
        If fCell.Address = Found1 Then Exit Do
    Loop

    With Rng
        .Borders.Weight = 2.5
        .Borders.Color = RGB(255, 0, 0)
    End With

    Exit Sub
NothingFound:
    MsgBox "No values were found in Column B"

End Sub
I also agree that Conditional Formatting would be the way to go; however, to follow up on your "if a VBA solution is wanted" idea, I think this macro will be even faster than what you posted...
Code:
Sub FindJohn()
  Application.ReplaceFormat.Clear
  With Application.ReplaceFormat
    .Borders(xlEdgeTop).Color = RGB(255, 0, 0)
    .Borders(xlEdgeTop).Weight = 2.5
    .Borders(xlEdgeBottom).Color = RGB(255, 0, 0)
    .Borders(xlEdgeBottom).Weight = 2.5
    .Borders(xlEdgeLeft).Color = RGB(255, 0, 0)
    .Borders(xlEdgeLeft).Weight = 2.5
    .Borders(xlEdgeRight).Color = RGB(255, 0, 0)
    .Borders(xlEdgeRight).Weight = 2.5
  End With
  Columns("B").Replace "John", "", xlWhole, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub
 
Last edited:
Upvote 0
This is a bit shorter


Code:
Sub FindJohn()
  Application.ReplaceFormat.Clear
  With Application.ReplaceFormat
    .Borders.Color = RGB(255, 0, 0)
    .Borders.Weight = 2.5
  End With
  Columns("B").Replace "John", "", xlWhole, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub
 
Upvote 0
This is a bit shorter
Code:
Sub FindJohn()
  Application.ReplaceFormat.Clear
  With Application.ReplaceFormat
    .Borders.Color = RGB(255, 0, 0)
    .Borders.Weight = 2.5
  End With
  Columns("B").Replace "John", "", xlWhole, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub
Thanks for noting this. I had made a mental note to myself to try that, but ran into a problem with the original method I came up with and spent time working out the kinks... and then completely forgot about my mental note.:oops:
 
Upvote 0
Agreed...I seem to leave mental notes all over the place.....I blame my birth certificate !!!
 
Upvote 0
...I blame my birth certificate !!!
Me too (mine, not yours)! I recently turned 71 and I don't remember having a problem with my memory when I was younger (of course, I could simply have forgotten:eek:)
 
Last edited:
Upvote 0
Happy belated Birthday Rick. I guess I have something to look forward to, losing my memory I mean.

Thank you all for the responses. I'll try all...
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top