I would like to change the color of the active cell to
a specified color when the focus is in that cell and then
when it moves to another revert to white.

also if the cell has another sheet name in it that when
the user selects that cell the active sheet is changed to
the new sheet

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nigel Bennett" wrote in message
...
I would like to change the color of the active cell to
a specified color when the focus is in that cell and then
when it moves to another revert to white.

also if the cell has another sheet name in it that when
the user selects that cell the active sheet is changed to
the new sheet

Hi Nigel
This works but needs to be placed in the ThisWorkbook object.



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Range("A1:M20").Interior.ColorIndex = 0
Target.Interior.ColorIndex = 7
If Left(Target, 5) = "Sheet" Then Sheets(Target.Value).Activate
End Sub

Regards
Andrew Bourke


Nigel Bennett wrote:
I would like to change the color of the active cell to
a specified color when the focus is in that cell and then
when it moves to another revert to white.

also if the cell has another sheet name in it that when
the user selects that cell the active sheet is changed to
the new sheet

Hi Nigel
This works but needs to be placed in the ThisWorkbook object.



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Range("A1:M20").Interior.ColorIndex = 0
Target.Interior.ColorIndex = 7
If Left(Target, 5) = "Sheet" Then Sheets(Target.Value).Activate
End Sub

Regards
Andrew Bourke


Nigel Bennett wrote:

I would like to change the color of the active cell to a specified
color when the focus is in that cell and then when it moves to another
revert to white.

also if the cell has another sheet name in it that when the user
selects that cell the active sheet is changed to the new sheet

Hi Nigel
This works but needs to be placed in the ThisWorkbook object.



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Range("A1:M20").Interior.ColorIndex = 0
Target.Interior.ColorIndex = 7
If Left(Target, 5) = "Sheet" Then Sheets(Target.Value).Activate
End Sub

Regards
Andrew Bourke




Nigel Bennett wrote:
I would like to change the color of the active cell to
a specified color when the focus is in that cell and then
when it moves to another revert to white.

also if the cell has another sheet name in it that when
the user selects that cell the active sheet is changed to
the new sheet