Can anyone point me in the right direction with this Macro.

I'm trying to get some Conditional Formatting through VBA when I download
data fron an ODBC link.

As it stands, the Macro below (which I got from the Forum) colours the cells
correctly if the data is keyed / pasted into the Spreadsheet, but NOT after
an ODBC download.

Can anyone assist?



Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1:C50")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "A": .Interior.ColorIndex = 3
Case "B": .Interior.ColorIndex = 4
Case "C": .Interior.ColorIndex = 5
Case "D": .Interior.ColorIndex = 6
Case "E": .Interior.ColorIndex = 7
Case "F": .Interior.ColorIndex = 8
Case "G": .Interior.ColorIndex = 9
Case "H": .Interior.ColorIndex = 10
Case "I": .Interior.ColorIndex = 11
Case "J": .Interior.ColorIndex = 12
Case "K": .Interior.ColorIndex = 13

End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

Not sure which event would work in your case, but the Worksheet_Change does
not appear to be the correct one. Maybe Worksheet_Activate? Or tag this
macro onto the tail end of your download code.

"Francois via OfficeKB.com" wrote:

Can anyone point me in the right direction with this Macro.

I'm trying to get some Conditional Formatting through VBA when I download
data fron an ODBC link.

As it stands, the Macro below (which I got from the Forum) colours the cells
correctly if the data is keyed / pasted into the Spreadsheet, but NOT after
an ODBC download.

Can anyone assist?



Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1:C50")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "A": .Interior.ColorIndex = 3
Case "B": .Interior.ColorIndex = 4
Case "C": .Interior.ColorIndex = 5
Case "D": .Interior.ColorIndex = 6
Case "E": .Interior.ColorIndex = 7
Case "F": .Interior.ColorIndex = 8
Case "G": .Interior.ColorIndex = 9
Case "H": .Interior.ColorIndex = 10
Case "I": .Interior.ColorIndex = 11
Case "J": .Interior.ColorIndex = 12
Case "K": .Interior.ColorIndex = 13

End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

JLGWhiz wrote:
Not sure which event would work in your case, but the Worksheet_Change does
not appear to be the correct one. Maybe Worksheet_Activate? Or tag this
macro onto the tail end of your download code.

Can anyone point me in the right direction with this Macro.

[quoted text clipped - 34 lines]
Application.EnableEvents = True
End Sub


I decided to try a different method:-

' Refresh ODBC
Range("D8").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("D8").Select



For I = 1 To 100

With ActiveSheet.Cells(I, 4) 'COL 4 = COL D

If .Value = "123" Then
.Interior.ColorIndex = 3

ElseIf .Value = "456" Then
.Interior.ColorIndex = 4

ElseIf .Value = "789" Then
.Interior.ColorIndex = 5

ElseIf .Value = "abc" Then
.Interior.ColorIndex = 6

'etc


End If
End With
Next


I don't suggest it's smart, only that it works.

Thanks for your reply, and to others on my wrongly posted thread

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1