Interior Color Macro

[Guest]

On line six below, the interior color should be set to 36 only when:
1. Cell B38 is selected (by the "GoTo" macro)
2. The worksheet is active.
So, when another cell is selected, or the user leaves the worksheet, the
cell returns to its original interior color.

How should this code be modified?

Sub GoToTFMChangeRequestWorksheetComptroller()
Application.ScreenUpdating = False
Sheets("TFM Change Request Worksheet").Select
Application.Goto Reference:=Range("A38"), Scroll:=True
Application.Goto Reference:=Range("B38"), Scroll:=False 'Places curser off
screen
Selection.Interior.ColorIndex = 36
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub

 

[Nigel]

You could put this code behind the worksheet affected, the event trigger
will test if cell B38 is selected and change the color, else reset
it.........

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$38" Then
Range("B38").Interior.ColorIndex = 36
Else
Range("B38").Interior.ColorIndex = 0
End If
End Sub

 

[Guest]

Hi Nigel,

Your code works exactly as intended. Thanks.
Can we take this a step further? In the worksheet there are now five merged
cell ranges that need to be treated the same way: A3:M3; A23:M23; A38:M38
(instead of the single cell B38); A49:M49; and A55:M55. What would the code
be?
Thanks, Phil

 

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A3,A23,A38,A49,A55"
Dim cell As Range

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = 36
End With
Else
For Each cell In Me.Range(WS_RANGE)
cell.Interior.ColorIndex = xlColorIndexNone
Next cell
End If

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks Bob, it works perfectly! Appriciate your time and expertise.