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.
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.
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
MsgBox Rng. Address
before the end.
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!