Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format using VBA not activating when needed
In the following procedure, how do I have the formatting occur as soon as
the operator exits the cell (either by the keyboard or mouse)? At the moment this procedure requires the operator to exit and then activate that cell again. Rob Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("K:K")) Is Nothing Then Exit Sub If Me.Cells(Target.Row, "K") = "" Then With ActiveCell.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = xlNone End With End If If Me.Cells(Target.Row, "K") = "AH" Then With ActiveCell.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = 45 .Pattern = xlSolid End With End If End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format using VBA not activating when needed
Hi Rob
It's not working as the target is the destination cell/range, that's why you have to click out then go back to modify row K. I'd work this by storing the activate range on startup using worksheet_activate in say variable1 and on a selection change put this range in variable2, then check to see if variable1 matches criteria, if yes run code, if not set variable1 = variable2. Alternatively have a look at the change event. hth keith |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format using VBA not activating when needed
Keith,
Thankyou for that explanation. I went for your alternative suggestion, by changing from Worksheet_SelectionChange to Worksheet_Change, and it works great. Rob "Keith74" wrote in message ... Hi Rob It's not working as the target is the destination cell/range, that's why you have to click out then go back to modify row K. I'd work this by storing the activate range on startup using worksheet_activate in say variable1 and on a selection change put this range in variable2, then check to see if variable1 matches criteria, if yes run code, if not set variable1 = variable2. Alternatively have a look at the change event. hth keith |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format using VBA not activating when needed
You've got it working on the active cell and not the target cell, try this:-
Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("K:K")) Is Nothing Then Exit Sub If Me.Cells(Target.Row, "K") = "" Then With Target.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = xlNone End With End If If Me.Cells(Target.Row, "K") = "AH" Then With Target.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = 45 .Pattern = xlSolid End With End If Mike "RobN" wrote: In the following procedure, how do I have the formatting occur as soon as the operator exits the cell (either by the keyboard or mouse)? At the moment this procedure requires the operator to exit and then activate that cell again. Rob Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("K:K")) Is Nothing Then Exit Sub If Me.Cells(Target.Row, "K") = "" Then With ActiveCell.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = xlNone End With End If If Me.Cells(Target.Row, "K") = "AH" Then With ActiveCell.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = 45 .Pattern = xlSolid End With End If End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format using VBA not activating when needed
Mike,
I tried that but it doesn't make any difference. I still need to enter a value in the cell, exit it and reactivate that cell for the formatting to work. Rob "Mike H" wrote in message ... You've got it working on the active cell and not the target cell, try this:- Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("K:K")) Is Nothing Then Exit Sub If Me.Cells(Target.Row, "K") = "" Then With Target.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = xlNone End With End If If Me.Cells(Target.Row, "K") = "AH" Then With Target.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = 45 .Pattern = xlSolid End With End If Mike "RobN" wrote: In the following procedure, how do I have the formatting occur as soon as the operator exits the cell (either by the keyboard or mouse)? At the moment this procedure requires the operator to exit and then activate that cell again. Rob Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("K:K")) Is Nothing Then Exit Sub If Me.Cells(Target.Row, "K") = "" Then With ActiveCell.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = xlNone End With End If If Me.Cells(Target.Row, "K") = "AH" Then With ActiveCell.Offset(0, -10).Resize(1, 16).Interior .ColorIndex = 45 .Pattern = xlSolid End With End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time format help needed | Excel Worksheet Functions | |||
Activating wrap text format causes my text to disappear, why? | Excel Discussion (Misc queries) | |||
IF / Else Format help needed | Excel Worksheet Functions | |||
Help needed to format x-axis of graph | Excel Discussion (Misc queries) | |||
Help needed with date format | Excel Discussion (Misc queries) |