Highlight selected cell

[Guest]

Hello,
Is that possible to do this:

When the cell is selected, it will highlight with a color, when is not
selected, the color back to normal?

For example: C1 is selected, highlight as blue, when select C3, C3 is
highlight as blue, and C1 back to normal.

Thanks

 

[Bob Phillips]

Here is one way, but be warned if wipes out any conditional formatting you
may have


'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow
.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
.Interior.ColorIndex = 20
End With
End With
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
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

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

Take a look at

http://cpearson.com/excel/rowliner.htm

 

[Guest]

Check out this link...

http://www.cpearson.com/excel/RowLiner.htm

 

[Guest]

Thank you so much!

The code is working, I deleted the code for the border,
But can I change the code: "with .entireRow" to the specify cells?

 

[Bob Phillips]

This is amendable to your number of rows and columns

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Const NUM_ROWS As Long = 5 '<=== change to suit
Const NUM_COLS As Long = 5 '<=== change to suit
Dim nStartRow As Long
Dim nStartCol As Long

Cells.FormatConditions.Delete
nStartRow = Target.Row - (NUM_ROWS \ 2)
If nStartRow < 1 Then nStartRow = 1
nStartCol = Target.Column - (NUM_COLS \ 2)
If nStartCol < 1 Then nStartCol = 1
With Me
With .Cells(Target.Row, nStartCol).Resize(, NUM_COLS)
.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
.Interior.ColorIndex = 20
End With
End With
With .Cells(nStartRow, Target.Column).Resize(NUM_ROWS)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End With

With Target
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you so much!

I changed the Const NUM_ROWS As Long = 1
Const NUM_COLS As Long = 1

And works good.

 

[Bob Phillips]

LOL!

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

I laughed, because if you only want the one cell it is much simpler

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Dim nStartRow As Long
Dim nStartCol As Long

Cells.FormatConditions.Delete

With Target
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)