Color formatting using a worksheet change event

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Thanks Hans. I am bit slow in learning, but trying my best.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Yes, that would be more efficient.

Reply | Quote

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:

Is it possible to change the sheet tab color with same approach,if the cell value changes as the result of a formula.
?

Reply | Quote

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

Reply | Quote

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.

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.

Reply | Quote

Please explain clearly and in detail what you want. Just posting a macro doesn’t give me a clue.

Reply | Quote

Please explain clearly and in detail what you want. Just posting a macro doesn’t give me a clue.

This topic was discussed sometime ago in another thread. I will continue the same.

Reply | Quote

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.

Reply | Quote

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…..

Reply | Quote

You can change the line

For Each oCell In UsedRange.Cells

to

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

Reply | Quote

Thank you, Hans for your help…..you (and others, like Don or StuartR) are always so quick to help and so patient….it is very much appreciated.

Reply | Quote