Simplify

[ccl28]

Hi,

Anyone can simplified the following vba code.

Thanks.


Private Sub HoleSizeSelection_Click()
If Range("G9").Value = "3" Then
Range("B12").Value = "Small"
Range("B13").Value = "Medium"
Range("B14").Value = "Large"
Range("B11").ClearContents
Range("D11").ClearContents
Range("C11").ClearContents
Range("E11").ClearContents
Range("C12").ClearContents
Range("E12").ClearContents
Range("C13").ClearContents
Range("E13").ClearContents
Range("D14").ClearContents

Range("C11").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

End With

Range("E11").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

End With
Range("C12").Select
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


Range("C12").Select
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

Range("E12").Select
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


End If

If Range("G9").Value = "4" Then
Range("B11").Value = "Pin"
Range("B12").Value = "Small"
Range("B13").Value = "Medium"
Range("B14").Value = "Large"
Range("D11").Value = "<= D <="
Range("C11").ClearContents
Range("E11").ClearContents
Range("C12").ClearContents
Range("E12").ClearContents
Range("C13").ClearContents
Range("E13").ClearContents
Range("D14").ClearContents

Range("C11").Select
Selection.Interior.ColorIndex = 2
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
Range("E11").Select
Selection.Interior.ColorIndex = 2
With Selection.Borders(xlEdgeLeft)
Selection.Interior.ColorIndex = 2
.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
End If
End Sub

[NickHK]

Something like this, not checked, but you get the idea:
If Range("G9").Value = "3" Then
Range("B12").Value = "Small"
Range("B13").Value = "Medium"
Range("B14").Value = "Large"
Range("B11:E11,C12:13,E12:E13,D14").ClearContents

With Range("C11,E11")
With .Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
.BorderAround xlLineStyleNone
End With

Range("C12,E12").BorderAround xlContinuous, xlThin,
xlColorIndexAutomatic

ElseIf Range("G9").Value = "4" Then
Range("B11").Value = "Pin"
Range("B12").Value = "Small"
Range("B13").Value = "Medium"
Range("B14").Value = "Large"
Range("D11").Value = "<= D <="
Range("C11:C13,E11:E13,D14").ClearContents

With Range("C11,E11")
With .Interior
.ColorIndex = 2
.Pattern = xlSolid
End With
.BorderAround xlLineStyleNone
End With
Else
MsgBox "Unhandled value"
End If

NickHK

"ccl28" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> Anyone can simplified the following vba code.
>
> Thanks.
>
>
> Private Sub HoleSizeSelection_Click()
> If Range("G9").Value = "3" Then
> Range("B12").Value = "Small"
> Range("B13").Value = "Medium"
> Range("B14").Value = "Large"
> Range("B11").ClearContents
> Range("D11").ClearContents
> Range("C11").ClearContents
> Range("E11").ClearContents
> Range("C12").ClearContents
> Range("E12").ClearContents
> Range("C13").ClearContents
> Range("E13").ClearContents
> Range("D14").ClearContents
>
> Range("C11").Select
> With Selection.Interior
> ColorIndex = 40
> Pattern = xlSolid
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> Selection.Borders(xlEdgeLeft).LineStyle = xlNone
> Selection.Borders(xlEdgeTop).LineStyle = xlNone
> Selection.Borders(xlEdgeBottom).LineStyle = xlNone
> Selection.Borders(xlEdgeRight).LineStyle = xlNone
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
>
> End With
>
> Range("E11").Select
> With Selection.Interior
> ColorIndex = 40
> Pattern = xlSolid
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> Selection.Borders(xlEdgeLeft).LineStyle = xlNone
> Selection.Borders(xlEdgeTop).LineStyle = xlNone
> Selection.Borders(xlEdgeBottom).LineStyle = xlNone
> Selection.Borders(xlEdgeRight).LineStyle = xlNone
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
>
> End With
> Range("C12").Select
> 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
>
>
> Range("C12").Select
> 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
>
> Range("E12").Select
> 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
>
>
> End If
>
> If Range("G9").Value = "4" Then
> Range("B11").Value = "Pin"
> Range("B12").Value = "Small"
> Range("B13").Value = "Medium"
> Range("B14").Value = "Large"
> Range("D11").Value = "<= D <="
> Range("C11").ClearContents
> Range("E11").ClearContents
> Range("C12").ClearContents
> Range("E12").ClearContents
> Range("C13").ClearContents
> Range("E13").ClearContents
> Range("D14").ClearContents
>
> Range("C11").Select
> Selection.Interior.ColorIndex = 2
> 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
> Range("E11").Select
> Selection.Interior.ColorIndex = 2
> With Selection.Borders(xlEdgeLeft)
> Selection.Interior.ColorIndex = 2
> 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
> End If
> End Sub
>
>
> --
> ccl28
> ------------------------------------------------------------------------
> ccl28's Profile:
http://www.excelforum.com/member.php...o&userid=36095
> View this thread: http://www.excelforum.com/showthread...hreadid=571702
>