Border formatting row of cells
This code below of your is working wonders!! Very pleased with it in the early testing stages. Looks like it will really do the trick.
I am calling it from the my code that sets the Day and the Desk, I assume that is how you intended it to be used.
Questions for my own enlightenment(in order as the appear in the code):
1. This returns C because the cell address is $C$n, second char = C ?
If Mid(myRng.Address, 2, 1) = "C" Then
2. With 0 it did nothing but works well with -4142. No problem with that is there?
If rngC.Interior.ColorIndex = -4142 Then
'If rngC.Interior.ColorIndex = 0 Then
3. You can see the lower half of the code is commented out and it still works perfect. Was that a typo of sort or what?
Howard
'/ with range created by cell X1 and cell Y1 try:
'/ and with skipping the colored cells try:
Sub TestCBSkipColor()
'/ by Claus
Dim myRng As Range
Dim rngC As Range
With Sheets("BLANK")
Set myRng = Intersect(Range(.Range("X1")), _
Range(.Range("X1")).Rows(Mid(.Range("Y1"), _
InStr(.Range("Y1"), " ") + 1, 99)))
End With
If Mid(myRng.Address, 2, 1) = "C" Then
For Each rngC In myRng
If rngC.Interior.ColorIndex = -4142 Then
'If rngC.Interior.ColorIndex = 0 Then
With rngC
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = IIf(Not WorksheetFunction.IsOdd(rngC.Column), _
xlHairline, xlThin)
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = IIf(WorksheetFunction.IsOdd(rngC.Column), _
xlHairline, xlThin)
End With
End With
End If
Next
'Else
' For Each rngC In myRng
' If rngC.Interior.ColorIndex = 0 Then
' With rngC
' With .Borders(xlEdgeLeft)
' .LineStyle = xlContinuous
' .ColorIndex = xlAutomatic
' .Weight = IIf(WorksheetFunction.IsOdd(rngC.Column), _
' xlHairline, xlThin)
' End With
' With .Borders(xlEdgeTop)
' .LineStyle = xlContinuous
' .ColorIndex = xlAutomatic
' .Weight = xlThin
' End With
' With .Borders(xlEdgeBottom)
' .LineStyle = xlContinuous
' .ColorIndex = xlAutomatic
' .Weight = xlThin
' End With
' With .Borders(xlEdgeRight)
' .LineStyle = xlContinuous
' .ColorIndex = xlAutomatic
' .Weight = IIf(Not WorksheetFunction.IsOdd(rngC.Column), _
' xlHairline, xlThin)
' End With
' End With
' End If
' Next
End If
With Sheets("BLANK")
.Range(.Range("X1")).BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlMedium
End With
End Sub
|