Conditional formating Not Updating unless you click run

[GRIFFIJ]

Hi

Hi I have a problem with my spreadsheet I have applied conddition formating but for som reason it won't update unless you click run in VBA code window I can see this to be a pain


My code is as follows:

Private Sub Worksheet_Calculate()
Dim oCell As Range
For Each oCell In Range("A1:A20")
Select Case oCell.Value
Case Is < 1
oCell.Interior.ColorIndex = xlNone
Case Is = 1
oCell.Interior.ColorIndex = 5
Case Is = 2
oCell.Interior.ColorIndex = 3
Case Is = 3
oCell.Interior.ColorIndex = 6
Case Is = 4
oCell.Interior.ColorIndex = 4
Case Is = 5
oCell.Interior.ColorIndex = 7
Case Is = 6
oCell.Interior.ColorIndex = 15
Case Is = 7
oCell.Interior.ColorIndex = 40
Case Is > 7
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub

 

[Rofeu]

Hi,

code doesn't run by itself unless you write it in an event clause.

you need to put your code in the change event of the worksheet.

Cheers,

Roel

 

[GRIFFIJ]

What would the code be?

 

[xlbo]

The code would be the same but would be wrapped in the worksheet change event rather than the calculate event

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question