Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Help (2007)

    I am using this code which works but I have to repeat this code 30 times and change the reference
    cells each time which takes a lot of time. is there anyway of shortening this code?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("J24") Then
    If Range("J24").Value = 0 Then
    Range("N18:AJ24").Select
    With Selection.Font
    .Size = 1
    End With
    End If
    If Range("J24").Value = 1 Then
    Range("N18:AJ24").Select
    With Selection.Font
    .Size = 1
    End With
    Range("n1824").Select
    With Selection.Font
    .Size = 8
    End With
    End If
    If Range("J24").Value = 2 Then
    Range("N18:AJ24").Select
    With Selection.Font
    .Size = 1
    End With
    Range("n18:t24").Select
    With Selection.Font
    .Size = 8
    End With
    End If
    If Range("J24").Value = 3 Then
    Range("N18:AJ24").Select
    With Selection.Font
    .Size = 1
    End With
    Range("n18:x24").Select
    With Selection.Font
    .Size = 8
    End With
    End If
    If Range("J24").Value = 4 Then
    Range("N18:AJ24").Select
    With Selection.Font
    .Size = 1
    End With
    Range("n18:Ab24").Select
    With Selection.Font
    .Size = 8
    End With
    End If
    If Range("J24").Value = 5 Then
    Range("N18:AJ24").Select
    With Selection.Font
    .Size = 1
    End With
    Range("n18:af24").Select
    With Selection.Font
    .Size = 8
    End With
    End If
    If Range("J24").Value = 6 Then

    Range("n18:aj24").Select
    With Selection.Font
    .Size = 8
    End With

    End If
    End If
    End sub


    I have also tried using this code but can not make it work
    Dim a, b, c, as interger
    a = j24, b = n18, c = aj24 .Etc so I could just change the a,b and c values each time Help!

    VB Learn
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help (2007)

    Welcome to Woody's Lounge!

    You can simplify the code by
    - Starting out to set the font size of N18:AJ24 to 1, and then adjust depending on the value of J24.
    - Not selecting ranges.
    - Using Select Case ... End Select instead of repeated If ... End If blocks.

    The code could look like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J24")) Is Nothing Then
    Range("N18:AJ24").Font.Size = 1
    Select Case Range("J24").Value
    Case 1
    Range("N18:P24").Font.Size = 8
    Case 2
    Range("N18:T24").Font.Size = 8
    Case 3
    Range("N18:X24").Font.Size = 8
    Case 4
    Range("N18:AB24").Font.Size = 8
    Case 5
    Range("N18:AF24").Font.Size = 8
    Case 6
    Range("N18:AJ24").Font.Size = 8
    End Select
    End If
    End Sub

    Another option is to use a variable to specify the last column to be set to size 8:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strCol As String
    If Not Intersect(Target, Range("J24")) Is Nothing Then
    Select Case Range("J24").Value
    Case 1
    strCol = "P"
    Case 2
    strCol = "T"
    Case 3
    strCol = "X"
    Case 4
    strCol = "AB"
    Case 5
    strCol = "AF"
    Case 6
    strCol = "AJ"
    End Select
    Range("N18:AJ24").Font.Size = 1
    Range("N18:" & strCol & "24").Font.Size = 8
    End If
    End Sub

  3. #3
    New Lounger
    Join Date
    Jul 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Help (2007)

    Great thanks I will give it a try, Hans is there any E books you would recomed to help teach myself VB in Excel?

    thanks
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help (2007)

    See <post:=249,920>post 249,920</post:> for links to some VBA tutorials (the first two are free, I think, the last one isn't). Another free one is at Anthony's Excel VBA Page.

    You'll find others by searching Google for excel vba tutorial.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •