Worksheet change doesn't work completely

[hamblin.adam]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

I've got this function for each cell in the range "n7:is66"....

=IF(AND(N$2>=$K7,N$2<=$L7),IF($G7="On Track",2,IF($G7="Issues",
3,IF($G7="At Risk",4,IF($G7="Completed",5,1)))),"")

and because conditional formating is too limited, I've got the below
VBA changing the colors when $G7 through $G66 changes, it works fine
when g7 is changed, but in order for the cells in n7:is66 to chang
color, I have to click each one individually in order for them to
change to their new color. So, how can these cells change color
everytime colomn G on their row changes automatically?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("n7:is66")) Is Nothing Then
Select Case Target
Case 1
icolor = 41
Case 2
icolor = 10
Case 3
icolor = 6
Case 4
icolor = 3
Case 5
icolor = 1
Case Else
'whatever
End Select
End If
Target.Interior.ColorIndex = icolor

End Sub

 

[Don Guillett]

Duh. If you put something in a selectionchange event it stand to reason that
you would not expect it to fire until you select the cell.....

 

[Mike H]

Try putting your macro in the worksheet_change event instead of the
wotksheet_Selectionchange that you have it in now.

Mike

 

[hamblin.adam]

When I use Private Sub Worksheet_Change the cell will not change at
all, when I noticed that it at least changes if I click on it when
using the SelectionChange, i just left it. I'm very new at any
coding. So, I thank you for your help.

 

[Dave Peterson]

Since those cells each contain formulas, the worksheet_Change event won't help
either.

But you could use the worksheet_Calculate event (still behind that worksheet).

Option Explicit
Private Sub Worksheet_Calculate()

Dim myCell As Range
Dim myRng As Range
Dim iColor As Long

Set myRng = Me.Range("N7:is66")

For Each myCell In myRng.Cells
Select Case myCell.Value
Case 1: iColor = 41
Case 2: iColor = 10
Case 3: iColor = 6
Case 4: iColor = 3
Case 5: iColor = 1
Case Else
'whatever
End Select
myCell.Interior.ColorIndex = iColor
Next myCell

End Sub


Be aware that with this many cells, you may find that each calculation takes
longer that you expect.

 

[hamblin.adam]

Okay this works good, it does take a few seconds to update, but I'm
okay with it.Thanks.....
But, the cells in this range have stopped refering to the AND
statement below ...

=IF(AND(N$2>=$K7,N$2<=$L7),IF($G7="On Track",2,IF($G7="Issues",
3,IF($G7="At Risk",4,IF($G7="Completed",5,1)))),"")

there is a begining date and an ending date for each row. In the
range N7:is66 each cell represents a day. if the day that each cell
represents doesn't fall between the date range, then it is not
colored. The code you helped me with stopped doing this. It's
ignoring the dates that the AND statement is refering to above....


Adam

 

[Dave Peterson]

I'd guess that your data isn't what it appears to be.

Maybe you're looking at the wrong cells. Maybe the dates are text (and not
really dates). Maybe the dates aren't what you think they are (give them an
unambiguous format: dd-mmm-yyyy) to check.