Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi all...I have the following code in a worksheet and I am using it to color cells gray when they say "Closed". The range is quite large, and each time I leave the wsheet and return, I have to wait for the code to run (which takes about 10-15 secs). I tried changing Activate to Change and it gave me an error message saying that "The procedure event does not match the description of the event or the procedure having the same name".....and changing Activate to Calculate is worse b/ceach change causes the code to run. I want to re-name or re-write (?) the event so that it only runs when I enter "Closed" in a cell (and does not run each time I leave/return to the sheet or make some other kind of change on the sheet....any suggestions?


    Private Sub Worksheet_Activate()
    Dim oCell As Range
    For Each oCell In Range("e12:dc272").Cells
    Select Case oCell
    Case "Closed"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 1
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    End Sub

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have you tried using Conditional Formatting on the Range e12:dc272
    With a simple Cell Value Condition of "Closed" and setting the colour to Grey.
    Then IF it is NOT closed the default Cell Colour will apply
    Andrew

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    I am using Excel 2003 and I have used up all 3 cond format rules.....that's the only reason why I even used the worksheet event....

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this? It checks for just the cells in E12C272 that have changed and only acts on those.

    Steve

    Code:
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim oCell As Range
      Dim rng As Range
      Set rng = Intersect(Target, Range("e12:dc272"))
      If Not rng Is Nothing Then
    	For Each oCell In rng
      	Select Case oCell
        	Case "Closed"
          	oCell.Interior.ColorIndex = 15
          	oCell.Font.ColorIndex = 1
        	Case Else
          	oCell.Interior.ColorIndex = xlColorIndexAutomatic
          	oCell.Font.ColorIndex = 1
      	End Select
    	Next oCell
      End If
      Set oCell = Nothing
      Set rng = Nothing
    End Sub


    PS: Note that VB is case sensitive so that "Closed" <> "closed". If you want it to be insensitive to case change the lines to:

    Select Case UCase(oCell)
    Case "CLOSED"

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thanks...I will try that and let you know

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Steve....that works like a charm........can you tell me where I can read/learn more about VBA.....I know a bit about the Worksheet Change event stuff, but for example, the actual code you suggested 'looks' quite similar to what I was using, and yet is remarkably different.....is there a website or book that explains in normal language what the various procedures will do etc etc...and answer questions about whether/how to have 2 WS change events for 1 wsheet....and explain the differneces b/w Option Explicit and Worksheet Change etc etc....and explains what things like DIM etc etc mean...? Thanks.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The code looks alike since I purposely used your code. The code essentially does the same thing, the difference in performance is due to the times the code is being run.

    The sheet activate runs whenever the worksheet is activated and it changes the format all 26,883 cells in the Range("e12:dc272") whether they have changed or not. The code I posted, examines the same range, but only acts on the cells when a cell on sheet has changed and only changes the formatting on those cells in the range that have changed.

    As for recommendations
    Check example code generated on this site (and other sites) by others and ask questions as appropriate. I find the best way to learn is to do things.

    I recommend getting a good book on excel/VB programming (John Walkenbach has some good ones). I have not looked at all of them but can recommend his "Power Programming" book.

    Here are some links (excel and Excel VBA), that I have especially found useful:

    John Walkenbach's spreadsheet page is Here

    Allen Wyatt posts online help obtained from his weekly excel tips newsletter. There are links on that site to subscribe. there is a free version and a "premium" (you pay a nominal fee) for additional tips and a discount on some of his e-books and collections of tips. You can also contact him about joining his "daily excel tips" which is an email program where people get Q&A in their email and people discuss.

    Jan Karel Pieterse has an excellent series of Excel Articles

    Here is Chip Pearson Topic Index(if you go to the bottom, you can see the search). Good Excel "primers" on many subjects.

    Debra Dagliesh's "Contextures" has lots of tips and techniques, many code examples for using excel's built-in routines.

    Jon Peltier's Excel Charts has some impressive tricks for manipulating charts and making some very interesting ones. (not a lot of VB but still good tips and tricks)


    VBA in particular
    Here is a link to some MS articles with links to How to use Visual Basic for Applications in Excel

    Some MS MVP articles:
    Creating a macro with no programming experience using the recorder [Using the recorder is an excellent way to learn about the VB object model. I often use the recorder to generate some example code and then generalize and simplify it. The coding is not the most efficient, usually (and there are some things like IFs and and LOOPing that it can not handle) but it can often get you 50-80% there.]

    Getting To Grips With VBA Basics In 15 Minutes

    Look at this List of documents by category Search under "spreadsheet", for plenty of good "articles".

    Hope this helps.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    I can recommend the following books:

    "Writing Excel Macros" by Steve Roman - O'Reilly -- The one I have is for 97 & 2000 so there may be an updated version. ISBN: 1-56592-587-4

    "VBA for Dummies" by Steve Cummings - Hungry Minds -- Actually not to bad but also a bit dated (2001) ISBN:0-7645-0856-3

    "VB & VBA In A Nutshell" by Paul Lomax - O'Reilly -- This is basically a reference book. ISBN: 1-56592-358-8

    "Special Edition Using Excel Visual Basic for Applications, 2nd Ed" by Jeff Webb -- QUE - Again a Reference and rated at the Accomplished to Expert level. ISBN: 0-7897-0269-X

    I'll also second the recommendation on the Walkenbach books.

    You could probably get all these and more used on Half.com or somewhere else. I find that no one author covers everything and if you really want to get into VBA the more your read the better off you'll be. Unfortunately, most books only have about 20% or less original content so there is a lot or repetition, but that can be a good thing...makes it sink in.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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