Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Counting text and launching macro problems

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I hope this is thorough enough (as will be evident in a moment), I am a complete novice working from books.

    I am trying to devise a macro to lauch when the cell status changes on an excel worksheet(the input is text). Also I have a working model of the procedure I am going to use but it does not fit with all the requirements I would LIKE to have (but it will do). An enhancement that would be handy is for the macro to look at the whole range (not just a single cell) and count the number of times text appears in the given range. Based on the result shade or not shade (see below).

    This macro I will copy to fill in a whole month of 9 shift 24 hour periods.

    /////////////////////////////////////////////

    Sub StoreObject()
    Sheets("Sheet2").Select
    myObject = Range("E7")
    Set myObject = Range("E7")
    If myObject <> Empty Then
    Range("E7:F7,E7:H7,I11:L11,M15:P15").Select
    With Selection.Interior
    .ColorIndex = 34
    .Pattern = xlSolid
    End With
    End If
    End
    End Sub

    /////////////////////////////////////////////

    Thanks for any insights - Just to be clear, My primary question is how to launch the macro when the cell changes.

    Kotting

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Kotting

    For your code try:

    Sub StoreObject()

    If Not IsEmpty(Sheets("Sheet2").Range("E7")) Then
    With Range("E7:F7,E7:H7,I11:L11,M15:P15").Interior
    .ColorIndex = 34
    .Pattern = xlSolid
    End With
    End If

    End Sub


    For your primary question, right click on the sheet name tab, select "View Code" and try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
    'Your Code Here

    End If
    End Sub


    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-25 07:15 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you!

    I will try it out.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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