Simple VBA code question

[percy83]

Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I’ve got the following code in my workbook. The cells in rng1 below contains a formula that will display “days until deadline” figure. Problem is that I need to refresh the whole sheet for the color to change even though the figure in the cell changes from one case to another. <o></o>
<o> </o>
Please enlighten me.<o></o>
<o> </o>
<o>Thanks! </o>
Best regards<o></o>
Per<o></o>
<o> </o>

Private Sub Worksheet_Change(ByVal Target As Range)<o:p></o:p>
Dim Cell As Range<o:p></o:p>
Dim Rng1 As Range<o:p></o:p>
Set Rng1 = Intersect(Range("J2:J300,O2:O300"), Target)<o:p></o:p>
If Not Rng1 Is Nothing Then<o:p></o:p>
    For Each Cell In Rng1<o:p></o:p>
        Select Case Cell.Value<o:p></o:p>
        Case vbNullString<o:p></o:p>
                Cell.Interior.ColorIndex = xlNone<o:p></o:p>
                Cell.Font.Bold = False<o:p></o:p>
                Cell.Font.ColorIndex = 1<o:p></o:p>
                Cell.Font.Name = "arial"<o:p></o:p>
                <o:p></o:p>
            Case -3000 To 0<o:p></o:p>
                Cell.Interior.ColorIndex = 3<o:p></o:p>
                Cell.Font.Name = "arial narrow"<o:p></o:p>
                Cell.Font.Bold = True<o:p></o:p>
                Cell.Font.ColorIndex = 2<o:p></o:p>
                <o:p></o:p>
                Case 1 To 14<o:p></o:p>
                Cell.Interior.ColorIndex = 6<o:p></o:p>
                Cell.Font.Name = "arial narrow"<o:p></o:p>
                Cell.Font.Bold = True<o:p></o:p>
                Cell.Font.ColorIndex = 1<o:p></o:p>
            <o:p></o:p>
            Case 15 To 1000<o:p></o:p>
                Cell.Interior.ColorIndex = 10<o:p></o:p>
                Cell.Font.Name = "arial narrow"<o:p></o:p>
                Cell.Font.Bold = True<o:p></o:p>
                Cell.Font.ColorIndex = 2<o:p></o:p>
                <o:p></o:p>
            Case Else<o:p></o:p>
                Cell.Interior.ColorIndex = xlNone<o:p></o:p>
                Cell.Font.Bold = False<o:p></o:p>
                Cell.Font.Name = "arial narrow"<o:p></o:p>
                Cell.Font.ColorIndex = 1<o:p></o:p>
        End Select<o:p></o:p>
    Next<o:p></o:p>
 End If<o:p></o:p>
End Sub

 

[Andrew Poulsom]

The Worksheet_Change event fires when a cell is changed by the user. It does not fire when the value in a cell that contains a formula changes. So Target can never be one of your formula cells. The Worksheet_Calculate event fires when the value in a cell that contains a formula changes.

 

[percy83]

OK so how do I apply this to my vba code? I can't just change from worksheet_change to worksheet_calculate that doesent work.

Pls help me?

Thanks in advance!
P

 

[Andrew Poulsom]

You can't just replace Change with Calculate because the Worksheet_Calculate event procedure doesn't have a Target argument. To get a correctly constructed procedure stub, in the VBE module window select Worksheet from the left hand drop down and Calculate from the right hand dropdown.