a way to color the CURRENT row or cell when selected

[vasilef]

I have a table with many data and I would like to have the CURRENT cell or row colored for an easier view.
The below macro is working well but it deactivates both the borders and colors.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Borders().LineStyle = xlNone
Cells.Interior.ColorIndex = 0
ActiveCell.EntireRow.BorderAround xlDash, ColorIndex:=5
ActiveCell.EntireRow.Interior.ColorIndex = 36
End Sub

any ideas how to keep the borders and colors? are there any other methods?
Thanks!!!

[mangesh_yadav]

change it to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = 0
ActiveCell.Interior.ColorIndex = 36
End Sub

Note: If you have any cells colored previously, then they disappear on using this macro.

Do you want to retain other colors...?

- Mangesh

[mangesh_yadav]

Use the following to retain your older colors:


Dim myAddress
Dim myColor

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Not IsEmpty(myAddress) Then
Range(myAddress).Interior.ColorIndex = myColor
End If

myAddress = ActiveCell.Address
myColor = ActiveCell.Interior.ColorIndex
ActiveCell.Interior.ColorIndex = 36

End Sub



- Mangesh

[vasilef]

Thanks! You solved the problem with the cell coloring.
Is it possible to keep the sheet colors and yet to color the whole row when I select it?

[mangesh_yadav]

That would become quite complicated as the program has to remember the colors in all cells in that row

Mangesh

[mangesh_yadav]

Try this:

Dim n
Dim myRow
Dim myColors(1 To 20)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

n = 20 ' nos of cells in row whose formatting is to be remembered


If Not IsEmpty(myRow) Then
For j = 1 To n
Cells(myRow, j).Interior.ColorIndex = myColors(j)
Next j
End If

myRow = ActiveCell.Row
For i = 1 To n
myColors(i) = Cells(myRow, i).Interior.ColorIndex
Next i

ActiveCell.EntireRow.Interior.ColorIndex = 36

End Sub



This remembers the colors in first 20 cells of the row. Replace 20 in code to suit your requirement

Mangesh

[vasilef]

Congratulation! It works really well. I replaced 20 with 256 which is the maximum and still works fine.

Well done!!!!!!!!

I just hope the people to whom I'm going to send the file won't save the file with the colored line after closing it.

[mangesh_yadav]

Good it works. My only fear for using 256 was the time factor. Thanks for the feedback

Mangesh

[haneira]

How can deactivate the function Sub Worksheet_SelectionChange?

[Nick Hebb]

If you want the currently selected range/cell to be colored and the
color cleared from the previously selected range or cell, then you will
need to keep track of that last range selected.

Declare a private variable at the module level, and set its range upon
activation. Then when SelectionChange is called, clear the last range,
color the target range and set the last range variable to the current
target, as follows

Private lastRange As Range ' module level declaration

Private Sub Worksheet_Activate()
Set lastRange = ActiveCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lastRange.Interior.ColorIndex = xlColorIndexNone
Target.Interior.Color = RGB(255, 255, 0) ' yellow
Set lastRange = Target
End Sub

[Ivan F Moala]

This maybe useful for you ??

http://office.microsoft.com/en-us/as...366231033.aspx

If conditionall formating is a problem ... then here is an alternative.

http://www.xcelfiles.com/Excel02.html#anchor_67