Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Worksheet change event & cond. formatting (Excel 2003)

    Hi loungers....I have a workbook with 2 sheets...on sheet 1, I have a worksheet change event code that changes the color of the cell to green when I enter the word TBA (for 'to be announced').....when I delete the TBA, it goes back to no interior color.....it is called WORKSHEET 1 in the attached zipped folder.

    On sheet 2, the TBA entries appear as a result of a formula that links cells from sheet 1 to sheet 2.....I am attaching the color formatting code that I am using for that event; it is called WORSHEET 2 in the attached zipped folder.

    ....my questions are this:
    (1) how can I modify these codes so that they will work on sheet 1 and sheet 2 when the sheets are password protected? Do I use "ActiveSheet. Unprotect:="xxx" and if so, where do I put it in the code?
    (2) sheet 2 is also p/word protected; the TBA entries that appear on page 2 don't turn green...is that b/c they are getting to sheet 2 via a formula, rather than being directly entered onto sheet 2? Is there a way to make the code work on sheet 2?...............and, I can't use the typical conditional formatting route b/c I already have 3 cond formatting codes (which is why I am doing it with a worksheet change event)...any help is always appreciated...thank you.
    Attached Files Attached Files

  2. #2
    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

    Re: Worksheet change event & cond. formatting (Excel 2003)

    1) Add the unprotect w/ pwd after the IF line. Add the protect with password before the END IF line.

    2) Change events only are triggered when the cell is changed, not the values in the cell. Since the formula is not changed, just the values, it will not trigger the event nor be a part of the target of other cells are changed.

    A workaround would be to do the interesection for the IF on the cells that would trigger the formulas to change and then in your For.. Next loop through all the formulas not just an intersection.

    The other workaround would be to run the code from the calculation event and loop through all the formatted formulas though this will run the code more than you might need to do it and could make the workbook sluggish.

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Worksheet change event & cond. formatting (Exc

    Hi Steve....I don't know what you mean with your workarounds....what would the 1st workaround look like, embedded in the code that I already have in worksheet 2 ?

  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

    Re: Worksheet change event & cond. formatting (Exc

    It depends on what the formulas look like. You didn't provide enough information to give any more than a generic answer.

    In essence, you must examine the formulas that are changing and see what cells in the formula will affect the value, then these cells must be monitored by the change event.

    For example, If you have in Cell C1 a formula: = A1+B1 in the cell, your intersect must check for changes in A1 and B1 as they will change the value in the fomula. Monitoring a change in C1 will not trigger the event. It can get complicated if A1 or B1 contain formulas as well, since you will have to keep progressing outwards to check for cells that change, not formulas.

    Perhaps you could put a sample file of what you want and we could detail it for that to give you an idea.

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Worksheet change event & cond. formatting (Exc

    Hi Steve...here is a sample of what I am working on, with notes on each page showing how I have set it up so far....does this explain it more clearly....
    Attached Files Attached Files

  6. #6
    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

    Re: Worksheet change event & cond. formatting (Exc

    IN Worksheet 2 you only need to check for changes in C3:N3 and if there are any changes, check the formatting

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("c3:n3")) Is Nothing Then
    For Each oCell In Range("c4:n7").Cells
    Select Case oCell
    Case "TBA", "tba", "Tba"
    oCell.Interior.ColorIndex = 4
    oCell.Font.ColorIndex = 1
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    End If
    End Sub</pre>


    But since Worksheet2 is dependent on changes in Worksheet1, when those changes are made, you must reformat worksheet2:
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("c4:f15")) Is Nothing Then
    ActiveSheet.Unprotect
    For Each oCell In Intersect(Target, Range("c4:f15")).Cells
    Select Case oCell
    Case "TBA", "tba", "Tba"
    oCell.Interior.ColorIndex = 4
    oCell.Font.ColorIndex = 1
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    End If

    If Not Intersect(Target, Range("B4:F15")) Is Nothing Then
    For Each oCell In Worksheets("Worksheet 2").Range("C4:N7")
    Select Case oCell
    Case "TBA", "tba", "Tba"
    oCell.Interior.ColorIndex = 4
    oCell.Font.ColorIndex = 1
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    End If
    ActiveSheet.Protect
    End Sub</pre>


    Steve

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Worksheet change event & cond. formatting (Exc

    Hi Steve....I am a bit confused...you say that worksheet 2 needs to be reformatted, but looking at the formulae, it suggests that the first one goes with worksheet2...and the second, longer formula goes with worksheet 1...and that it is worksheet 1 that needs the reformatting (ps: and when you say reformatting, I assume you're referring to the 'coloring' of the cell interiors and font)??..??

    ...and when you say that in "IN Worksheet 2 you only need to check for changes in C3:N3..".....the changes don't occur in the row containing the dates, they occur in range C3:N7....

  8. #8
    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

    Re: Worksheet change event & cond. formatting (Exc

    You are looking for changes in the VALUES (not the cell contents) of C4:N7. They are formulas so the values change when the values of C11:N16 change. The values in C11:N16 are also formulas and their values change when the values in <font color=red>B3:N3</font color=red> in Worksheet2 occur OR the values in <font color=red>Worksheet 1!B4:F15</font color=red>change.

    Thus the cells in Worksheet2!C4:N7 must be reformated when either Worksheet2!B3:N3 occurs or when the contents of Worksheet 1!B4:F15 change. Thus the Worksheet2 code must examine for changes in B3:N3 and change the formatting in Worksheet2!C4:N7 (which the code for Worksheet2 does).

    In addition the cells in Worksheet 1!B4:F15 must be monitored for changes and when they occur, they must also set the formatting of Worksheet 2!C4:N7. Since we are monitioring changes in Worsheet 1, the code must be added to the worksheet change event in worksheet 1. But the code must set the format for the cells in worksheet 2. This is what the 2nd part of the worksheet1 code does (the first part is what you had).

    Steve

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Worksheet change event & cond. formatting (Exc

    OK...now I think that I understand....I used the code you suggested and it worked fine, on both worksheets, until I saved the file....then when I reopened it, it stopped working....one thing I did was format the gridlines to make them gray (b/c they would disappear if I entered TAB and then later erased it).....so I manually added gray gridlines and they stay in place even when TAB is erased....I am attaching a sample of the file with the new code in it, if you wouldn't mind taking a look at it.....could the gltich be the fact that it is protected?
    Attached Files Attached Files

  10. #10
    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

    Re: Worksheet change event & cond. formatting (Exc

    I forgot to unprotect and reprotect worksheet2....

    Try this:

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("c4:f15")) Is Nothing Then
    ActiveSheet.Unprotect
    For Each oCell In Intersect(Target, Range("c4:f15")).Cells
    Select Case oCell
    Case "TBA", "tba", "Tba"
    oCell.Interior.ColorIndex = 4
    oCell.Font.ColorIndex = 1
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    End If
    ActiveSheet.Protect
    If Not Intersect(Target, Range("B4:F15")) Is Nothing Then
    Worksheets("Worksheet 2").Unprotect
    For Each oCell In Worksheets("Worksheet 2").Range("C4:N7")
    Select Case oCell
    Case "TBA", "tba", "Tba"
    oCell.Interior.ColorIndex = 4
    oCell.Font.ColorIndex = 1
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    End If
    Worksheets("Worksheet 2").Protect
    End Sub</pre>


    Steve

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Worksheet change event & cond. formatting (Exc

    It still brings up the following error message/gets stuck at this point:

    oCell.Interior.ColorIndex = xlColorIndexAutomatic

    ?????

  12. #12
    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

    Re: Worksheet change event & cond. formatting (Exc

    Which section is it in and what do you do that causes it to occur?

    I add the code and make changes to worksheet1 and I don't get an error...

    Steve

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Worksheet change event & cond. formatting (Exc

    Sorry...my mistake..I meant to send an attachment with the code...when you enter tab, it doesn;t go green...and yet it did at first..??..??
    Attached Files Attached Files

  14. #14
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Worksheet change event & cond. formatting (Exc

    Hi Steve...I got it to work and wanted to let you know....it sure makes for a sluggish workbook, once you get the formatting working on worksheet2.....when I only use in on wroksheet1 (as I started with) it ddi the w/sheet 1 coloring very quickly.....now it is quite sluggish, so I willl have to show the user(s) and see if they want it working on both sheets, or, just the one....thank you for your help (again!)..I iwll let you know how it goes with this in real life....

  15. #15
    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

    Re: Worksheet change event & cond. formatting (Exc

    Why would it go "green" with "tab"? It is not in the case Select. The code tests for "tba"...

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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