Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi loungers...I am using the following code (that I got from Hans) as a worksheet change event on Sheet 1 ... it triggers color changes to cells on Sheet 1 when you input the data shown. My question follows this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("e13:y272")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("e13:y272")).Cells
    If UCase(oCell) Like "CON*" Then
    oCell.Interior.ColorIndex = 5
    oCell.Font.ColorIndex = 2
    Else
    Select Case oCell
    Case "Away", "away", "N/S", "n/s"
    oCell.Interior.ColorIndex = 4
    oCell.Font.ColorIndex = 1
    Case "Xvac", "xvac", "Xcon", "xcon"
    oCell.Interior.ColorIndex = 1
    oCell.Font.ColorIndex = 2
    Case "VAC", "vac", "Vac"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 1
    Case "CJ"
    oCell.Interior.ColorIndex = 3
    oCell.Font.ColorIndex = 2
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    oCell.Font.ColorIndex = 1
    End Select
    End If
    Next oCell
    End If
    End Sub

    ........using formula on Sheet 2, the data from Sheet 1 appears on Sheet 2 in a slightly different format....I would like the colors that appear on Sheet 1 to also appear on Sheet 2....simply copying this code to Sheet 2 doesn't do the trick (presumably b/c it relates to entries made on Sheet 1 only)....how can I modify this code to make it workable on Sheet 2 so that, for example, when VAC appears on Sheet 2, it is black-on-grey..??..?? etc etc....thank you for your help.

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Copying the code in sheet 2 & changing range in code from

    [codebox]If Not Intersect(Target, Range("e13:y272")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("e13:y272")).Cells[/codebox]

    to
    [codebox]If Not Intersect(Target, Range("a1:iv65536")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("a1:iv65536")).Cells[/codebox]

    will hopefully serve the purpose.
    Attached Files Attached Files
    Regards
    Prasad

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

    In Excel 97-2003, Range("A1:IV65536") is the entire sheet, so you can omit the line

    If Not Intersect(Target, Range("a1:iv65536")) Is Nothing Then

    and the corresponding End If - they serve no purpose. And the line

    For Each oCell In Intersect(Target, Range("a1:iv65536")).Cells

    can be simplified to

    For Each oCell In Target.Cells

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796544' date='06-Oct-2009 11:51']Prasad,

    In Excel 97-2003, Range("A1:IV65536") is the entire sheet, so you can omit the line

    If Not Intersect(Target, Range("a1:iv65536")) Is Nothing Then

    and the corresponding End If - they serve no purpose. And the line

    For Each oCell In Intersect(Target, Range("a1:iv65536")).Cells

    can be simplified to

    For Each oCell In Target.Cells[/quote]
    Thanks Hans. I am bit slow in learning, but trying my best.
    Regards
    Prasad

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Good morning Hans and Prasad......I am attaching Book4.xls (a live sample of my workbook, based on Prasad's Book3).....you'll see that entries on Sheet 1 flow thru to Sheet 2.....the coloring works on sheet 1 as it should....I have modified the code on sheet 2 to reflect the changes suggested, but the code does not produce the coloring in the same way that it does on sheet 1 (ie: it produces no change of color to any of the cells on sheet 2)......any ideas ?.....thanks.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The problem is that the Worksheet_Change event only occurs when the user modifies a cell value, not if the cell value changes as the result of a formula.

    Since Sheet2 is not modified directly by the user, you can't use the Worksheet_Change event. You should use the Worksheet_Calculate event; this occurs each time a formula is recalculated. Change the code for Sheet2 to the following:

    Code:
    Private Sub Worksheet_Calculate()
      Dim oCell As Range
      For Each oCell In UsedRange.Cells
    	If UCase(oCell) Like "CON*" Then
    	  oCell.Interior.ColorIndex = 5
    	  oCell.Font.ColorIndex = 2
    	Else
    	  Select Case oCell
    		Case "Away", "away", "N/S", "n/s"
    		  oCell.Interior.ColorIndex = 4
    		  oCell.Font.ColorIndex = 1
    		Case "Xvac", "xvac", "Xcon", "xcon"
    		  oCell.Interior.ColorIndex = 1
    		  oCell.Font.ColorIndex = 2
    		Case "VAC", "vac", "Vac"
    		  oCell.Interior.ColorIndex = 15
    		  oCell.Font.ColorIndex = 1
    		Case "CJ"
    		  oCell.Interior.ColorIndex = 3
    		  oCell.Font.ColorIndex = 2
    		Case Else
    		  oCell.Interior.ColorIndex = xlColorIndexAutomatic
    		  oCell.Font.ColorIndex = 1
    	  End Select
    	End If
      Next oCell
    End Sub
    As soon as you edit a cell on Sheet1, the cells on Sheet2 will be re-colored.

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    When I had to do something similar I used the Worksheet_Activate event to ensure that whenever the user looked at the second sheet it was up to date. This had much lower overhead.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='StuartR' post='796574' date='06-Oct-2009 13:38']When I had to do something similar I used the Worksheet_Activate event to ensure that whenever the user looked at the second sheet it was up to date. This had much lower overhead.[/quote]
    Yes, that would be more efficient.

  9. #9
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good morning Hans and Prasad......I am attaching Book4.xls (a live sample of my workbook, based on Prasad's Book3).....you'll see that entries on Sheet 1 flow thru to Sheet 2.....the coloring works on sheet 1 as it should....I have modified the code on sheet 2 to reflect the changes suggested, but the code does not produce the coloring in the same way that it does on sheet 1 (ie: it produces no change of color to any of the cells on sheet 2)......any ideas ?.....thanks.
    In addition to solution provided by Hans, it seems that something went wrong at your end while copy/renaming sheets, I guess.
    Attached Files Attached Files
    Regards
    Prasad

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796572' date='06-Oct-2009 16:56']The problem is that the Worksheet_Change event only occurs when the user modifies a cell value, not if the cell value changes as the result of a formula.

    Since Sheet2 is not modified directly by the user, you can't use the Worksheet_Change event. You should use the Worksheet_Calculate event; this occurs each time a formula is recalculated. Change the code for Sheet2 to the following:[/quote]
    Is it possible to change the sheet tab color with same approach,if the cell value changes as the result of a formula.
    ?
    Regards
    Prasad

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes - if you want the tab color to change the moment the cell value changes, use the Worksheet_Calculate event, and if it's OK if the tab color changes when the sheet is activated, use the Worksheet_Activate event.

    Code within the event procedure could look like this:

    Code:
      Select Case Range("B3").Value
    	Case "A"
    	  Me.Tab.ColorIndex = 3
    	Case "B"
    	  Me.Tab.ColorIndex = 4
    	Case "C"
    	  Me.Tab.ColorIndex = 5
    	Case Else
    	  Me.Tab.ColorIndex = 2
      End Select

  12. #12
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796579' date='06-Oct-2009 17:40']Yes - if you want the tab color to change the moment the cell value changes, use the Worksheet_Calculate event, and if it's OK if the tab color changes when the sheet is activated, use the Worksheet_Activate event.[/quote]
    I use following code to update the wb.

    [codebox]Sub changeweek()
    Dim varSheet
    For Each varSheet In Array("N1", "N2", "N3", "N4", "N5", "N6", "N8", "N9", "ANS", "ANTB")
    With Worksheets(varSheet)
    .Range("L4:L200").Copy
    .Range("S4").PasteSpecial Paste:=xlPasteValues
    .Tab.ColorIndex = xlColorIndexNone
    End With
    Next varSheet
    Sheets("Gr Summary").Select
    Range("D16:E22").Copy
    Range("E16").PasteSpecial Paste:=xlPasteValues
    Sheets("BG3 ").Select
    Range("R1").Copy
    Range("T1").PasteSpecial Paste:=xlPasteValues
    Range("R1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[2]+7"
    Range("R1").Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("M10:S10, M14:S14, M18:S18, M22:S22, M26:S26, M30:S30, _
    M34:S34, M45:S45, M49:S49, M53:S53").PasteSpecial Paste:=xlPasteFormulas
    Range("K40").Copy
    Range("M40:S40").PasteSpecial Paste:=xlPasteFormulas
    Range("A3:A6").Select
    End Sub
    [/codebox]

    The wb is updated using external links/formulas and I want to change the color of tab on updation of sheet. Pl guide me to make it possible.
    Regards
    Prasad

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Please explain clearly and in detail what you want. Just posting a macro doesn't give me a clue.

  14. #14
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you, Hans and StuartR............I have used the most recent code provided by Hans (and I changed it to be Private Sub Worksheet Activate ()........I think that somehow I have to confine it to range C4:IS28, b/c when I move from sheet 1 to sheet 2 (and it starts coloring the cells) it seems to be working its way through the entire worksheet (which takes a long, long time) and it is also shimmering or shaking (kind of like an old tv picture).....the coloring works as it should (ie the cells are colored correctly) but it doesn't seem to stop shaking and when I try to get it to stop, it says "NOT RESPONDING" and then I close Excel and lose my work.....

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can change the line

    For Each oCell In UsedRange.Cells

    to

    For Each oCell In Range("C4:IS28").Cells

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
  •