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

 

[macropod]

Hi JDB,

This is not the appropriate forum - it's for Word, not Excel.

Try calling your Worksheet_Change sub from the Worksheet_Calculate sub:
Option Explicit
Private Sub Worksheet_Calculate()
Dim i As Integer
For i = 1 To 10
Call Worksheet_Change(Cells(i, 1))
Next
End Sub

This way, the formatting will be applied regardless of whether the test
strings/values are generated in a way that triggers a recalculation.


Cheers

 

[JDB]

Thanks your help, and VERY, VERY sorry re wrong forum. late night
posting.