ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Simplify (https://www.excelbanter.com/excel-programming/370399-simplify.html)

ccl28[_10_]
Simplify
 

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

NickHK
Simplify
 
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" wrote in message
...

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


All times are GMT +1. The time now is 01:36 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com