Formatting cells entered by a macro

[Kirsty]

I have a macro, that selects the last row in a range of cells and inputs a
blank row just above this (as below).

I want to then format the cells I input, and am having trouble writing the
macro to select them

Can anyone help?


Dim LastRow As Long, NumberOfBlankRows
NumberOfBlankRows = 1
LastRow = Range("A3:R1000").Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Intersect(Range("A:R"), Rows(LastRow)).Resize(NumberOfBlankRows).Insert

 

[Rick Rothstein]

You didn't tell us what kind of format you wanted to apply to the cells, so
I am just assuming a custom date format... change the NumberFormat line to
suit your needs. Note that in the code below, I removed the
NumberOfBlankRows variable and the Resize event that used it... I wasn't
sure from the wording of your first thread on this subject whether you
wanted a user defined number of rows or just one to be inserted... your post
indicates you only wanted one, so the code can be simplified for that
condition.

Dim LastRow As Long, NumberOfBlankRows
LastRow = Range("A3:R1000").Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
With Intersect(Range("A:R"), Rows(LastRow))
.Insert
.Offset(-1).NumberFormat = "mmmm d, yyyy"
End With

By the way, note that I do not select the cells between columns A and R in
order to format them... there is no need to select the cells in order to
perform this operation on them. Also note that I used a With..End With block
so the Intersect operation did not have to be specified multiple times.

 

[Kirsty]

I am trying to format the borders, so I have a black border around each
square, is that possible?

 

[Rick Rothstein]

This code should do what you want...

Dim LastRow As Long, NumberOfBlankRows
LastRow = Range("A3:R1000").Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
With Intersect(Range("A:R"), Rows(LastRow))
.Insert
With .Offset(-1)
.BorderAround Weight:=xlThick
.Borders(xlInsideVertical).Weight = xlThick
End With
End With

In addition to the weight of the line, you can also control the color and
line style if desired.