Getting around Worksheet_Change()

M

mtowle

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
 
H

Harlan Grove

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?

Use the Calculate event handler. The Change event handler only runs
when you change the contents (formula) of cells. The Calculate event
handler can check specific values each time Excel recalcs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top