Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Simplify
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to simplify this... j5b9721 Excel Worksheet Functions 5 July 23rd 09 09:03 AM
A way to simplify this please Larry Empey[_2_] Excel Programming 3 July 1st 06 01:04 AM
please help simplify acarril[_12_] Excel Programming 0 September 27th 04 07:45 PM
please help simplify acarril[_10_] Excel Programming 1 September 21st 04 07:39 PM
please help simplify acarril[_9_] Excel Programming 1 September 21st 04 05:36 PM


All times are GMT +1. The time now is 02:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement