Excel Conditional Formatting

[JDB]

I pickedup the following code from
http://www.ozgrid.com/VBA/excel-conditional-formatting-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

 

[Bob Phillips]

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]

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]

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]

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