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