Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting through VBA ODBC
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting through VBA ODBC
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting through VBA ODBC
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |