Excel VBA Format a Range Borders to Crossed diagonal lines [Solved/Closed]

Report
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
-
MaxStart
Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
-
Hello,


please help me to format a range of cells like if in a range a1:c10 if any cell eg: c1 contains a value c then format diagonal lines with a thickness as I have shown uploaded a file.please help me if I can be done through vba.. I tried with conditional format but it has no option with this format.

3 replies

Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
53
With Selection.Borders(xlDiagonalDown)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .Weight = xlMedium
End With
With Selection.Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .Weight = xlMedium
End With


Je ne parle pas français, mais je l'aime.
je ne pas change.
4
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4085 users have said thank you to us this month

mathewmunna
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015

thanks for your reply.. I used ur code to check value c in the cell,
i am very new to vba tried with bellow one but got some errors.. kindly help me to fix this issue..

Sub Xmark()
For Each cell In Range("A10:G26")
If Sheets("Sheet1").Range("C") Then
With Selection.Borders(xlDiagonalDown)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = xlMedium
End With
With Selection.Borders(xlDiagonalUp)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = xlMedium
End With
End Sub
Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
53
This will work on range A1-C10
Sub Max()
Dim I As Integer, j As Integer
i = 1
j = 1
Do Until I = 11
    For j = 1 To 3
    If Cells(i, j).Value = "c" Then
        Cells(i, j).Select
        With Selection.Borders(xlDiagonalDown)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .Weight = xlMedium
        End With
        With Selection.Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .Weight = xlMedium
        End With
    End If
    Next j
i = I + 1
Loop
End Sub


Je ne parle pas français, mais je l'aime.
je ne pas change.
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015

I have another code but something is wrong in this code too


Private Sub format()
Dim R As Range, C As Range
Set R = Range("B5:G20")
Application.ScreenUpdating = False
For Each C In R
If Len(C.Text) = C Then
With Selection.Borders(xlDiagonalDown)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = xlMedium
End With
With Selection.Borders(xlDiagonalUp)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = xlMedium
End With
Else
End If
Next C
Application.ScreenUpdating = True
End Sub