Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,624
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,939
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default 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






  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default 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






  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default 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






  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default 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








  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default 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










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell won't highlight when selected TJAC Excel Discussion (Misc queries) 0 January 13th 09 08:53 PM
How do I have a cell highlight when selected? Rhonda Excel Worksheet Functions 12 September 7th 07 04:40 PM
Highlight selected cell GKW in GA Excel Discussion (Misc queries) 2 November 12th 06 01:47 PM
How do I have a cell highlight when selected? Rhonda Excel Discussion (Misc queries) 3 October 2nd 06 05:32 PM
How do I have a cell highlight when selected? Rhonda Excel Discussion (Misc queries) 0 October 2nd 06 05:12 PM


All times are GMT +1. The time now is 08:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"