Change Color of Cell

[Nigel Bennett]

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

 

[Bob Phillips]

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)

 

[Ajtb]

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

 

[Ajtb]

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

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

 

[Ajtb]

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