I pickedup the following code from
http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
-------------------
This works real well


How can I make this work with a Worksheet_Calculate subroutine?

I tried to play with it but could not make it work. I have a rather
large range of temperature values I would like to color as I change
different parameters.

Thanks for any help.

JDB

Why would you want to? With calculate, you would need to check each cell,
each value. Not as efficient.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"JDB" wrote in message
oups.com...
I pickedup the following code from
http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
-------------------
This works real well


How can I make this work with a Worksheet_Calculate subroutine?

I tried to play with it but could not make it work. I have a rather
large range of temperature values I would like to color as I change
different parameters.

Thanks for any help.

JDB

Here is my problem;
1. I have a finite difference analysis of fish temperatures -
2. Internal portions depend on exteranl temp and time in the oven
3. I want different colors to dispay different temp ranges
4. the Worksheet_change works if I directly enter the data but I have
something like 18,000 cells recalculating when I change a parameter.

What can you suggest that is more efficient than a re-calcuation event?

Thanks


JDB

Bob Phillips wrote:
Why would you want to? With calculate, you would need to check each cell,
each value. Not as efficient.

With Calculate, you will get 18,000 recalculating for every cell change. It
can only be slower.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"JDB" wrote in message
oups.com...
Here is my problem;
1. I have a finite difference analysis of fish temperatures -
2. Internal portions depend on exteranl temp and time in the oven
3. I want different colors to dispay different temp ranges
4. the Worksheet_change works if I directly enter the data but I have
something like 18,000 cells recalculating when I change a parameter.

What can you suggest that is more efficient than a re-calcuation event?

Thanks


JDB

Bob Phillips wrote:
Why would you want to? With calculate, you would need to check each
cell,
each value. Not as efficient.

Thanks your good advice.
I simply wrote a macro that runs from a command button to run the
conditional formatting code in question. 18,000 cell in about 4 sec,
so I can handle that.

JDB

For the record and anyone else looking at this, I am a hacker, but this
works for me.
-------------------------------
Private Sub CommandButton1_Click()
Call ConditionalFormat
End Sub


Private Sub ConditionalFormat()
Dim icolor As Integer
Dim R As Integer
Dim C As Integer

RangeName = "DataMatrix"

FCol = Range(RangeName).Column
FRow = Range(RangeName).Row

For R = FRow To 3200 Step 1
If Cells(R, FCol) = "" Then
Exit For
End If
For C = FCol To 64 Step 1
If Cells(R, C) = "" Then
Exit For
End If
TargetCell = Round(Cells(R, C), 0)

Select Case TargetCell
Case 0 To 28
icolor = 37
Case 29 To 60
icolor = 41
Case 61 To 90
icolor = 39
Case 91 To 120
icolor = 43
Case 121 To 150
icolor = 6
Case 151 To 180
icolor = 46
Case Else
icolor = 3
End Select

Cells(R, C).Interior.ColorIndex = icolor

Next C
Next R

End Sub
-----------------------------------------------






Bob Phillips wrote:
With Calculate, you will get 18,000 recalculating for every cell change. It
can only be slower.

--

HTH

Bob Phillips