Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell won't highlight when selected | Excel Discussion (Misc queries) | |||
How do I have a cell highlight when selected? | Excel Worksheet Functions | |||
Highlight selected cell | Excel Discussion (Misc queries) | |||
How do I have a cell highlight when selected? | Excel Discussion (Misc queries) | |||
How do I have a cell highlight when selected? | Excel Discussion (Misc queries) |