VBA Problem: Changing top border thickness if...

[Nepumbra]

Hello, I am pretty new to VBA and am trying to write something that will make the top border between B and G thin where the C column is not empty, and make the top border thick where the B column is not empty. If both C and B are not empty it should make the border thick. Here is what I did:

Sub Underline()

Dim rng As Range, C As Range, x As Range, B As Range, rng2 As Range, z As Range
Set C = Range("C2:C797")

For Each x In C
Set rng = Range("B" & x.Row & ":G" & x.Row)
Set B = Range("B" & x.Row)
If Not IsEmpty(B.Value) Then
rng.Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
ElseIf Not IsEmpty(x.Value) Then
rng.Select
With Selection.Borders(x1EdgeTop)
.LineStyle = x1Continuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = x1Thin
End With
End If
Next
End Sub

The problem occurs in the second "With Selection.Borders(x1EdgeTop)". It gives me a run time error 1004, Application defined error. No idea why...
Any help would be much appreciated!
Thanks!

 

[JLGWhiz]

If Not IsEmpty(B.Value) Then

Unless the value in B is also an Object variable, VBA will ignore this line of code. The IsEmpty function only applies to Object Variables to test if they have been initialized. You could Use:

If IsEmpty(B)

because you have made B an object variable representing a range. But it will not test the value of B, only whether B is initialized as an object. I think what you are trying to do is:

If B.Value = "" Then

which will check to see if there is a value > blank in that cell.

 

[JLGWhiz]

That should be:

If B.Value <> "" Then

 

[Nepumbra]

Thank you!

 

[JLGWhiz]

You're Welcome,
Regards, JLG

 

[Nepumbra]

I made the changes, but I still get an error on the line:

ElseIf x.Value <> "" Then
rng.Select
With Selection.Borders(x1EdgeTop)
.LineStyle = x1Continuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = x1Thin
End With
End If

Any ideas? I can't figure it out.