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

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.....

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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

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

Mike

" wrote:

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

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.

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.

wrote:

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

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

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.

wrote:

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