Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good afternoon

    I have the code below in a worksheet which works fine. I am trying to build an automatic report function that gathers data from various other worksheets by using the =sheet1!C6 formula for example. I have put this code into the VBA area for the report worksheet but nothing happens (I am expecting for example that if an H is entered for holiday on an employees summary and the cell is coloured red with a white font the same thing would happen on the report because the =sheet1!C6 would =H and the code below would be used)

    Hope that makes some sense

    Code:
    For Each cel In Range("C6:AG144").Cells
            If IsError(cel.Value) Then
                ' do you want to color these?
            Else
                Select Case UCase(cel.Value)
                    Case "H"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 3
                    Case "S"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 51
                    Case "M"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 26
                    Case "P"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 9
                    Case "U"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 1
                    Case "A"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 16
                    Case "B"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 13
                    Case "T"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 25
                    Case "C"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 49
                    Case "L"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 56
                    Case "X"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 2
    Case Else
                        cel.Font.ColorIndex = 1
                End Select
                End If
        Next cel
    End Sub
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The code needs to be either in a standard module or in the specific sheet's module. You cannot affect Sheet2's contents with a macro in Sheet1 where the macro in Sheet 1 does not specify the Sheet2's name.

  3. #3
    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
    Could you elaborate on what triggers the code now, what you want to trigger the code, and what it should affect, etc?

    In other words what do you want the code to do and when do want the code to do it?

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Mike and Steve for your responses

    I have attached a very stripped down version of a workbook in which I record all staff absences. Normally there would be a departmental summary page for all of the departments and different Countries and overall monthly summaries. For ease of posting I have stripped this down to one department and one month.

    In the DirectorsSummary worksheet you can either double click a cell to activate a hidden combo and select an absence type from the list or you can merely type the letter directly into a cell. The cell is then coloured according to the absence type and the initial of the absence placed into the cell, for example a full days holiday would give a red cell with a capital H whereas a half days holiday would give a red cell with a small h.

    These figures are then reported into personal employee summaries where I count the number of days of each type of absence so I know what holiday is left and if I should be doing something about a persons sickness record (a separate sheet removed here scores staff sick records against the Bradford Factor). All departments and Countries are record on the monthly summaries and sent to department heads for planning purposes on the last day of each month.

    What I wanted to achieve is when an absence is selected by using the combo or direct typing in the departmental summary and the cell is coloured according to the codes shown in my first post the personal and monthly summaries would show the same. I thought that by adding the code and referencing the correct cells it would do it but that is obviously not the case.

    Thanks
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    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
    To do what I think you want will make your spreadsheet even more sluggish than it is now. But what you need to do is to move all the code from the 3 "worksheet change" to 3 routines in a general module:

    Sub UpdateJanSum()
    For Each cel In Worksheets("January Summary").Range("C6:AG13").Cells
    'rest of code from Jan summary Change event
    Next cel
    End Sub

    Sub UpdateDirSum()
    For Each cel In Worksheets("Directors Summary").Range("C6:AG170").Cells
    'rest of code Dir Summary Change event
    Next cel

    End Sub

    Sub UpdateDir()
    For Each cel In Worksheets("Directors").Range("D6:AH147").Cells
    'rest of code from Dir Change event
    Next cel
    End Sub

    Create a new code:
    Sub UpdateAllSheets()
    UpdateDir
    UpdateDirSum
    UpdateJanSum
    End sub

    Now in all the 3 Change event codes, can become:
    Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateAllSheets
    End

    And any changes to any of those 3 sheets will update all the worksheets (which will it very sluggish....)

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    To make your Change event more efficient, have the macro only change the changed cells instead of checking all of the cells:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    	If Intersect(Target, Range("C6:AG13")) = False Then
     	Exit Sub
    	Else
    '	For Each target In Range("C6:AG13").Cells
     	If IsError(Target.Value) Then
     	' do you want to color these?
     	Else
     	Select Case UCase(Target.Value)
     	Case "H"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 3
     	Case "S"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 51
     	Case "M"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 26
     	Case "P"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 9
     	Case "U"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 1
     	Case "A"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 16
     	Case "B"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 13
     	Case "T"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 25
     	Case "C"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 49
     	Case "L"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 56
     	Case "X"
     	Target.Font.ColorIndex = 2
     	Target.Interior.ColorIndex = 2
    Case Else
     	Target.Font.ColorIndex = 1
     	End Select
     	End If
    	End If
    End Sub

  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
    Testing the changed cells will only work for cells explicitly changed, it will not work for cell value changes of formulas (since the formula is not changed the cell is not changed, only the value). If any cells are changed on the sheet due to formulas and still require the color to be changed, they all have to be tested. This is especially true on the other sheets to update, since all seem to be formulas, which will require testing all the cells...

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sdckapr,
    Very true for the formula sheets. They won't get updated unless the values are checked for each cell. I was referring to the sheets where the values would be entered, not the results of a formula.

    stevehocking,
    The attached file grabs the Changed cells address, loops through the rest of the sheets of the workbook looking for the formula that points back to the changed cell =Directors!I8 for example. If the formula is found, it runs the color changing routine on the value of the cell. Finally it returns to the changed cell.

    The caveat is that only the first reference to the original cell will be changed.
    Attached Files Attached Files

  9. #9
    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
    Another way to speed it up is (in addition to only changing the actual changed cells as you suggest) is to NOT routinely update the other sheets when the Dir sheet is changed. There is no need to keep it constantly updated since it is not being viewed.

    The update procedures could be run when those worksheets are activated so they are updated right before they are viewed...

    Steve


  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Another way to speed it up is (in addition to only changing the actual changed cells as you suggest) is to NOT routinely update the other sheets when the Dir sheet is changed. There is no need to keep it constantly updated since it is not being viewed.

    The update procedures could be run when those worksheets are activated so they are updated right before they are viewed...

    Steve
    Hi Steve and Mike

    Thanks for all of you valuable help and input. I am slowly working my way through everything so that I understand it and not just copy it.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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