Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Borders around row containing date

0

I am in need of some help, I am trying to put a border around the row that contains the todays date located in column A. I have a semi woking code but cannot seem to fiqure it out. any help would be greatly appreciated! Test file included.

Answer
Discuss

Discussion

Hi Justin. I'm not near my PC today (so can't see your code ) but you can do that by Conditional Formatting, no code required. If that doesn't appeal, please add your code to your question and I'll take a look to see if there's any glaring errors 
John_Ru (rep: 6102) Mar 1, '22 at 9:32 am
Sub Datechange()
    Dim ws As Worksheet
    Dim TodaysDate As Date
    Dim Rng As Range
    Dim Rng1 As Range
    Set wsarray = Sheets(Array("SHEET2", "SHEET3"))
    For Each ws In wsarray
    Application.ScreenUpdating = False
    ws.Unprotect
    TodaysDate = CLng(Date)
  With Rows("1:4000")
  'For Yesterday -1
  Set Rng1 = ws.Range("a1:a4000")
    If Not Rng1 Is Nothing Then
    ws.Unprotect
    Rng1.EntireRow.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
    End If
    'For Today
  Set Rng = .Find(what:=TodaysDate)
 
   'Rng.EntireRow.BorderAround ColorIndex:=23, Weight:=xlThick
 
    
    End With
         ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    Next
        Application.ScreenUpdating = True
End Sub
Justin2010 (rep: 8) Mar 1, '22 at 9:36 am
Mmm. I don't use . Find often - doea it rwturn anything for Rng? Put
MsgBox Rng. Address
before the end. 
John_Ru (rep: 6102) Mar 1, '22 at 10:21 am
A$29 ---wich is todays date
Justin2010 (rep: 8) Mar 1, '22 at 10:25 am
every thing seems to work exept for this
'Rng.EntireRow.BorderAround ColorIndex:=23, Weight:=xlThick
Justin2010 (rep: 8) Mar 1, '22 at 10:28 am
Please try replacing the EntireRow bit with Resize(1, 20)
John_Ru (rep: 6102) Mar 1, '22 at 11:56 am
That worked. Thank you again!!
Justin2010 (rep: 8) Mar 1, '22 at 1:05 pm
Add to Discussion

Answers

0
Selected Answer

Justin

Your discussion above indicates that the problem code line is:

 Rng.EntireRow.BorderAround ColorIndex:=23, Weight:=xlThick
.

Not sure why that doesn't work given Rng is a found range (cell). If I try this in VB Explorer's Intermediate window:

Selection.EntireRow.BorderAround ColorIndex:=23, Weight:=xlThick
the row (s) of whatever cells I have selected are bounded by thick blue lines.

My (discussion) suggestion of:

 Rng.Resize(1, 20).BorderAround ColorIndex:=23, Weight:=xlThick
(which I chose to ensure a limited range is returned) produces the desired effect so we're both happy seemingly!
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login