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

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

<[email protected]> wrote in message
news:[email protected]...
> 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
>