+ Reply to Thread
Results 1 to 6 of 6

worksheet events and a little more help

  1. #1
    tinkerbellsmyhoe
    Guest

    worksheet events and a little more help

    Hello,

    I'll start with the code i am using already:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 6 And Target.Row > 3 And Target.Row < 256 And
    Target.Value = "F" Then
    Worksheets("Test Results").Select
    Worksheets("Test Results").Cells(2, Target.Row + 1).Select


    End If


    If Target.Count > 1 Then Exit Sub
    If Target.Column = 7 And Target.Row > 3 And Target.Row < 256 And
    Target.Value = "F" Then
    Worksheets("Test Results").Select
    Worksheets("Test Results").Cells(13, Target.Row + 1).Select
    End If


    If Target.Count > 1 Then Exit Sub
    If Target.Column = 8 And Target.Row > 3 And Target.Row < 256 And
    Target.Value = "F" Then
    Worksheets("Test Results").Select
    Worksheets("Test Results").Cells(24, Target.Row + 1).Select
    End If


    If Target.Count > 1 Then Exit Sub
    If Target.Column = 9 And Target.Row > 3 And Target.Row < 256 And
    Target.Value = "F" Then
    Worksheets("Test Results").Select
    Worksheets("Test Results").Cells(35, Target.Row + 1).Select
    End If


    If Target.Count > 1 Then Exit Sub
    If Target.Column = 10 And Target.Row > 3 And Target.Row < 256 And
    Target.Value = "F" Then
    Worksheets("Test Results").Select
    Worksheets("Test Results").Cells(46, Target.Row + 1).Select
    End If


    If Target.Count > 1 Then Exit Sub
    If Target.Column = 11 And Target.Row > 3 And Target.Row < 256 And
    Target.Value = "F" Then
    Worksheets("Test Results").Select
    Worksheets("Test Results").Cells(57, Target.Row + 1).Select
    End If


    If Target.Count > 1 Then Exit Sub
    If Target.Column = 12 And Target.Row > 3 And Target.Row < 256 And
    Target.Value = "F" Then
    Worksheets("Test Results").Select
    Worksheets("Test Results").Cells(68, Target.Row + 1).Select
    End If
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 13 And Target.Row > 3 And Target.Row < 256 And
    Target.Value = "F" Then
    Worksheets("Test Results").Select
    Worksheets("Test Results").Cells(77, Target.Row + 1).Select
    End If


    End Sub


    I would like to insert a piece of code into each if statement to change



    the target cell + the next 10 rows in that column to a red font. then
    change the font back to black (or default) after the selection changes.



    i have once again given my best shot and the best i can get (once i get



    passed the runtime errors), is for nothing to happen.


    Thanks in advance,


    Mike C.


  2. #2
    Bob Phillips
    Guest

    Re: worksheet events and a little more help

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With Target
    If .Count > 1 Then Exit Sub
    If .Row > 3 And .Row < 256 And .Value = "F" Then
    If .Font.ColorIndex = 3 Then
    .Resize(10, 1).Font.ColorIndex = xlColorIndexAutomatic
    Else
    .Resize(10, 1).Font.ColorIndex = 3 'red
    End If
    If .Column >= 6 And .Column <= 13 Then
    Worksheets("Test Results").Select
    Worksheets("Test Results").Cells(2 + (.Value - 6) * 11, .Row
    + 1).Select
    End If
    End If
    End If
    End With

    End Sub

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "tinkerbellsmyhoe" <[email protected]> wrote in message
    news:[email protected]...
    > Hello,
    >
    > I'll start with the code i am using already:
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 6 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(2, Target.Row + 1).Select
    >
    >
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 7 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(13, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 8 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(24, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 9 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(35, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 10 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(46, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 11 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(57, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 12 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(68, Target.Row + 1).Select
    > End If
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 13 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(77, Target.Row + 1).Select
    > End If
    >
    >
    > End Sub
    >
    >
    > I would like to insert a piece of code into each if statement to change
    >
    >
    >
    > the target cell + the next 10 rows in that column to a red font. then
    > change the font back to black (or default) after the selection changes.
    >
    >
    >
    > i have once again given my best shot and the best i can get (once i get
    >
    >
    >
    > passed the runtime errors), is for nothing to happen.
    >
    >
    > Thanks in advance,
    >
    >
    > Mike C.
    >




  3. #3
    Don Guillett
    Guest

    Re: worksheet events and a little more help

    try this
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row < 4 Or Target.Column < 7 Then Exit Sub

    Columns(Target.Column).Font.ColorIndex = 0
    Target.Resize(10, 1).Font.ColorIndex = 3

    'MsgBox Target.Column
    x = 2 + (Target.Column - 6) * 11
    MsgBox x
    Application.Goto Sheets("Test Results").Cells(x, Target.Row + 1)
    End Sub

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "tinkerbellsmyhoe" <[email protected]> wrote in message
    news:[email protected]...
    > Hello,
    >
    > I'll start with the code i am using already:
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 6 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(2, Target.Row + 1).Select
    >
    >
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 7 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(13, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 8 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(24, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 9 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(35, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 10 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(46, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 11 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(57, Target.Row + 1).Select
    > End If
    >
    >
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 12 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(68, Target.Row + 1).Select
    > End If
    > If Target.Count > 1 Then Exit Sub
    > If Target.Column = 13 And Target.Row > 3 And Target.Row < 256 And
    > Target.Value = "F" Then
    > Worksheets("Test Results").Select
    > Worksheets("Test Results").Cells(77, Target.Row + 1).Select
    > End If
    >
    >
    > End Sub
    >
    >
    > I would like to insert a piece of code into each if statement to change
    >
    >
    >
    > the target cell + the next 10 rows in that column to a red font. then
    > change the font back to black (or default) after the selection changes.
    >
    >
    >
    > i have once again given my best shot and the best i can get (once i get
    >
    >
    >
    > passed the runtime errors), is for nothing to happen.
    >
    >
    > Thanks in advance,
    >
    >
    > Mike C.
    >




  4. #4
    tinkerbellsmyhoe
    Guest

    Re: worksheet events and a little more help

    thanks for the replies gentlemen. i tried both pieces of code with my
    existing code and i having porblems with both. the code from Don
    doesnt spit out any errors but it doesnt seem to do anything. please
    help again.


  5. #5
    Bob Phillips
    Guest

    Re: worksheet events and a little more help

    What are the problems with mine?

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "tinkerbellsmyhoe" <[email protected]> wrote in message
    news:[email protected]...
    > thanks for the replies gentlemen. i tried both pieces of code with my
    > existing code and i having porblems with both. the code from Don
    > doesnt spit out any errors but it doesnt seem to do anything. please
    > help again.
    >




  6. #6
    Don Guillett
    Guest

    Re: worksheet events and a little more help

    both should work. You may want to change mine from

    Columns(Target.Column).Font.ColorIndex = 0
    to
    cells.Font.ColorIndex = 0

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "tinkerbellsmyhoe" <[email protected]> wrote in message
    news:[email protected]...
    > thanks for the replies gentlemen. i tried both pieces of code with my
    > existing code and i having porblems with both. the code from Don
    > doesnt spit out any errors but it doesnt seem to do anything. please
    > help again.
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1