Conditional Formatting and Borders

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jrew23 - 01 Mar 2005 18:03 GMT

I'm trying to add thick borderlines on my spreadsheet using conditional
formatting. But the "thick" option is not available. I observed the
code using the macro recorder which produced

Sub ConditionalFormat1()

Range("A5:O428").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=$A5<>$A6"
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With
End Sub

... I thought I could chagne the weight to xlMedium, but it didn't
work. Is there another option?

Reply to this Message

Dave Peterson - 01 Mar 2005 20:43 GMT

If you set this manually, you'll see that xlthin is as thick as you can get.

I think you're stuck--maybe make it a nice color????

> I'm trying to add thick borderlines on my spreadsheet using conditional
> formatting. But the "thick" option is not available. I observed the
[quoted text clipped - 15 lines]
> ... I thought I could chagne the weight to xlMedium, but it didn't
> work. Is there another option?

Signature

Dave Peterson

Reply to this Message

Jrew23 - 01 Mar 2005 21:59 GMT

My nice color is red. I'm just trying to format my report so it's
easier for people to see differences between accounts.

My sorted list is looks like this

xxxxx 123
xxxxx 321
yyyy 456
mmm 654
...

Is there a feature in excel that could automatically create 3 tabs for
each account?

So it appears as
tab1
xxxx
123
321

tab2
yyy
456

tab3
mmm
654

Reply to this Message

Bob R. - 01 Mar 2005 22:26 GMT

If you paste this into the sheet object it should do the same thing as
the original macro you posted above, but with thick lines.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim myRange As Range
Set myRange = Me.Range("A5:A6")
If Intersect(Target, myRange) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
If Range("A5").Value <> Range("A6").Value Then
Range("A5:O428").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
End If
If Range("A5").Value = Range("A6").Value Then
Range("A5:O428").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
End If
Application.EnableEvents = True
Target.Select

End Sub

Reply to this Message

Jrew23 - 01 Mar 2005 23:13 GMT

Bob, I tried it out and it applied thick borders on the outside table.
I'm trying to apply thick borders on the "xledgebottom" of each cell if
it doesn't equal the cell below it. I figure I could work wih your
code and revise it a bit... and it should do the trick. I'll give it a
try. Thanks

Reply to this Message