ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Highlight selected cell (https://www.excelbanter.com/excel-programming/396476-highlight-selected-cell.html)

hong
Highlight selected cell
 
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
Highlight selected cell
 
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)



"Hong" wrote in message
...
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



JE McGimpsey
Highlight selected cell
 
Take a look at

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

In article ,
Hong wrote:

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

Jim Thomlinson
Highlight selected cell
 
Check out this link...

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

Jim Thomlinson


"Hong" wrote:

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

hong
Highlight selected cell
 
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" wrote:

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)



"Hong" wrote in message
...
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
Highlight selected cell
 
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)



"Hong" wrote in message
...
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" wrote:

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)



"Hong" wrote in message
...
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

hong
Highlight selected cell
 
Thank you so much!

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

And works good.


"Bob Phillips" wrote:

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)



"Hong" wrote in message
...
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" wrote:

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)



"Hong" wrote in message
...
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
Highlight selected cell
 
LOL!

--
---
HTH

Bob

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



"Hong" wrote in message
...
Thank you so much!

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

And works good.


"Bob Phillips" wrote:

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)



"Hong" wrote in message
...
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" wrote:

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)



"Hong" wrote in message
...
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
Highlight selected cell
 
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)



"Bob Phillips" wrote in message
...
LOL!

--
---
HTH

Bob

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



"Hong" wrote in message
...
Thank you so much!

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

And works good.


"Bob Phillips" wrote:

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)



"Hong" wrote in message
...
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" wrote:

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)



"Hong" wrote in message
...
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


All times are GMT +1. The time now is 09:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com