Conditional formatting with imported data

[stephenfu1]

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

 

[Tom Ogilvy]

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.