Colouring Cells (Excel 2003 SP3)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote