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

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

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