Conditional formatting with imported data
 

Hi

I want to change the colour of a cell depending on its value - I can
get past the 3 condition limit in Excel by using the code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("AM2:AM1000")) Is Nothing Then Exit
Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Being Deployed": .Interior.ColorIndex = 45
'etc
End Select
End With

CleanUp:
Application.EnableEvents = True
End Sub


However this only works when i enter edit a cell - currently in my
spreadsheet i update cells by refreshing the import external data
query. This does not start the Worksheet_Change subroutine and
therefore the conditional formatting doesnt take place.

Any ideas on how i get past this?

cheers

Conditional formatting with imported data
 

Use the calcuclate event and alter you code to loop through all cells of
interest. This will be slower, but shouldn't be a big impact.

See Chip Pearson's page on events
(http://www.cpearson.com/excel/events.htm ) if you are not familiar with the
calculate event.
--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Hi

I want to change the colour of a cell depending on its value - I can
get past the 3 condition limit in Excel by using the code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("AM2:AM1000")) Is Nothing Then Exit
Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Being Deployed": .Interior.ColorIndex = 45
'etc
End Select
End With

CleanUp:
Application.EnableEvents = True
End Sub


However this only works when i enter edit a cell - currently in my
spreadsheet i update cells by refreshing the import external data
query. This does not start the Worksheet_Change subroutine and
therefore the conditional formatting doesnt take place.

Any ideas on how i get past this?

cheers