Error with Bold

  • Thread starter Thread starter crapit
  • Start date Start date
C

crapit

What's wrong?
With Worksheets("Recent")
Range(Cells(1, 1), Cells(1, 2)).Font.FontStyle = "Bold"
End with
 
Take a look at the activesheet. I bet that range is bolded.

But if you want Recent to be the sheet with the change, add a couple of dots.

With Worksheets("Recent")
.Range(.Cells(1, 1), .Cells(1, 2)).Font.FontStyle = "Bold"
End With

Without those dots, the range refers to the activesheet (if the code is in a
general module).
 
What about this?
Run-time error '1004':

Unable to set the LineStyle property of the Border class
 
The sub-procedure is called from another sub. Btw, how to check that a
procedure has completely executed its codes b4 other sub-procedure carry on

sub setup()
...
Other codes
...
Worksheets("Reply").Activate
Range(Cells(1, 1), Cells(lastrow, 6)).Select
borders
end sub

Sub borders
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
end sub
 
This has errors if Reply isn't the activesheet:

Worksheets("Reply").Activate
Range(Cells(1, 1), Cells(lastrow, 6)).Select

Try:

with worksheets("reply")
.activate
.Range(.Cells(1, 1), .Cells(lastrow, 6)).Select
borders
end with

Notice the dots in front of .range and .cells. That means that thing refers
back to the previous With object.

If you wanted, you could have written it:

worksheets("reply").Range(worksheets("reply").Cells(1, 1), _
worksheets("reply").Cells(lastrow, 6)).Select

(The With/End with structure is easier to type and (I find) easier to read.)

But you don't have to select stuff to work with it. But you'll have to change
at least part of your code:

Option Explicit
Sub setup()

Dim myRng As Range
Dim LastRow As Long

LastRow = 8 'for testing
'...
'Other codes
'...
With Worksheets("Reply")
Set myRng = .Range(.Cells(1, 1), .Cells(LastRow, 6))
End With
Call borders(myRng)

End Sub

Sub borders(rng As Range)
rng.borders(xlDiagonalDown).LineStyle = xlNone
rng.borders(xlDiagonalUp).LineStyle = xlNone
With rng.borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub


The sub-procedure is called from another sub. Btw, how to check that a
procedure has completely executed its codes b4 other sub-procedure carry on

sub setup()
...
Other codes
...
Worksheets("Reply").Activate
Range(Cells(1, 1), Cells(lastrow, 6)).Select
borders
end sub

Sub borders
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
end sub
Click to expand...
 
As I use border function on two worksheets, the 1st sheet work fine, but the
2nd doesnt. And the error happen at

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
 
Just a guess.

What do you have selected on that sheet?

xlInsideHorizontal means that you want horizontal borders between a multirow
range (like between A1 and A2).

If you only have one row, then that could be the problem.

Have you thought about getting rid of the .Selects and .activates and Selection
and applying the borders to the range directly (like that previous selection)?


As I use border function on two worksheets, the 1st sheet work fine, but the
2nd doesnt. And the error happen at

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Click to expand...
 
Never mind, I solve it.
But I run into this problem


Run- time error '1004'
Select method of range class failed

~~~~~~~~~~~~~~Codes~~~~~~~~
With Worksheets("Repeat")
.Rows("1:1").Select <----Error Highlight
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
Selection.Font.Bold = True
Cells.Select
With Selection.Font
.Size = 16
End With
End with
 
You can only select a range if the worksheet is active:

With Worksheets("Repeat")
.select '<--
.Rows("1:1").Select

But you still may want to go back through this thread and read about how to work
with ranges without selecting.

(but it looks like you don't want to.)

Never mind, I solve it.
But I run into this problem

Run- time error '1004'
Select method of range class failed

~~~~~~~~~~~~~~Codes~~~~~~~~
With Worksheets("Repeat")
.Rows("1:1").Select <----Error Highlight
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
Selection.Font.Bold = True
Cells.Select
With Selection.Font
.Size = 16
End With
End with
Click to expand...
 
Back
Top