Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change()
Spreadsheet cell (s) are linked to other cells/calculations. The cell in question will change value based on changes in values in OTHER cell and based on calculations. I am using the following code to updat colors in final cells, but Worksheet_change() does not allow fo changes based on calcs. Any ideas as to how I can update colors Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cel As Range For Each cel In Range("BB10:BF31").Cells If IsNumeric(cel.Value) Then If cel.Value = 1 And cel.Value <= 25 Then cel.Interior.ColorIndex = 4 ElseIf cel.Value = 26 And cel.Value <= 50 Then cel.Interior.ColorIndex = 35 ElseIf cel.Value = 51 And cel.Value <= 75 Then cel.Interior.ColorIndex = 6 ElseIf cel.Value = 76 And cel.Value <= 100 Then cel.Interior.ColorIndex = 3 End If ElseIf cel.Value = "N/A" Then cel.Interior.ColorIndex = 15 End If Nex -- mtowl ----------------------------------------------------------------------- mtowle's Profile: http://www.hightechtalks.com/m11 View this thread: http://www.hightechtalks.com/t226600 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change()
If you just want it based on calculations, how about teh Worksheet_Calculate
event. -- HTH RP (remove nothere from the email address if mailing direct) "mtowle" wrote in message ... Spreadsheet cell (s) are linked to other cells/calculations. The cells in question will change value based on changes in values in OTHER cells and based on calculations. I am using the following code to update colors in final cells, but Worksheet_change() does not allow for changes based on calcs. Any ideas as to how I can update colors? Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cel As Range For Each cel In Range("BB10:BF31").Cells If IsNumeric(cel.Value) Then If cel.Value = 1 And cel.Value <= 25 Then cel.Interior.ColorIndex = 4 ElseIf cel.Value = 26 And cel.Value <= 50 Then cel.Interior.ColorIndex = 35 ElseIf cel.Value = 51 And cel.Value <= 75 Then cel.Interior.ColorIndex = 6 ElseIf cel.Value = 76 And cel.Value <= 100 Then cel.Interior.ColorIndex = 3 End If ElseIf cel.Value = "N/A" Then cel.Interior.ColorIndex = 15 End If Next -- mtowle ------------------------------------------------------------------------ mtowle's Profile: http://www.hightechtalks.com/m114 View this thread: http://www.hightechtalks.com/t2266009 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change()
Use this ..
.... Application.EnableEvents = False ... your code .... Application.EnableEvents = True this should take care of your problem. MVM "mtowle" wrote: Spreadsheet cell (s) are linked to other cells/calculations. The cells in question will change value based on changes in values in OTHER cells and based on calculations. I am using the following code to update colors in final cells, but Worksheet_change() does not allow for changes based on calcs. Any ideas as to how I can update colors? Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cel As Range For Each cel In Range("BB10:BF31").Cells If IsNumeric(cel.Value) Then If cel.Value = 1 And cel.Value <= 25 Then cel.Interior.ColorIndex = 4 ElseIf cel.Value = 26 And cel.Value <= 50 Then cel.Interior.ColorIndex = 35 ElseIf cel.Value = 51 And cel.Value <= 75 Then cel.Interior.ColorIndex = 6 ElseIf cel.Value = 76 And cel.Value <= 100 Then cel.Interior.ColorIndex = 3 End If ElseIf cel.Value = "N/A" Then cel.Interior.ColorIndex = 15 End If Next -- mtowle ------------------------------------------------------------------------ mtowle's Profile: http://www.hightechtalks.com/m114 View this thread: http://www.hightechtalks.com/t2266009 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change | Excel Worksheet Functions | |||
Worksheet_change won't run | Excel Discussion (Misc queries) | |||
worksheet_change | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |