Format using VBA not activating when needed

[RobN]

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

 

[Keith74]

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

 

[Mike H]

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]

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

 

[RobN]

Keith,

Thankyou for that explanation. I went for your alternative suggestion, by
changing from Worksheet_SelectionChange to Worksheet_Change, and it works
great.

Rob