Conditional formatting based on selected row?

[mcomp72]

I am interested in having the font color in a particular cell change color if the user has selected any cell on that same row. (For example, if they select a cell anywhere on row 15, the font color in cell A15 would change to red. Otherwise, it would be black.) I was able to figure out how to do this using the Workbook_SelectionChange sub. However, I need to do it without using VBA code, because whenever the sub runs, it clears the clipboard and thus my users cannot do a Copy & Paste on the sheet. I have been trying to figure out how to make this work using Conditional Formatting, but so far haven't had any luck. I haven't been able to find any existing posts about this, so I'm not sure if it is even possible to do this using Conditional Formatting. Anyone know if it is, and if so, any idea how I would do it?

 

[ouvay]

Please provide a little more information about the expected behaviour.. do you expect the colours to change back to black or their previously assigned colour when leaving the row?

or would you like them to remain red?

Try this VBA Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Cells.Interior.ColorIndex = 0
 Dim MyRange As Range
 Set MyRange = Range("A:A").EntireRow
 MyRange.Font.ColorIndex = 1
 If Not Intersect(Target, MyRange) Is Nothing Then
  Cells(Target.Row, "A").Font.ColorIndex = 3
 End If
End Sub

This will return the A:A range to black - although, it will also prevent you to assign any colours in that range

 

[mcomp72]

I want the color to return to black when the user leaves the row.

Thank you for the reply. However, in my post I said that I needed to do this without VBA code. When the Worksheet_SelectionChange sub runs it is clearing the clipboard, so Copy & Paste is not possible on the worksheet, and my users sometimes need to be able to do that. So using VBA code for this is not a workable solution. That is why I am hoping to do it with Conditional Formatting.

 

[Peter_SSs]

Give this a try. It does involve vba but should still allow copy/paste.

1. Select all of column A by clicking its heading label
2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("row")=ROW() -> Format... -> On the Font tab select red -> OK -> OK
3. Put this code in the worksheet's module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

 

[mcomp72]

Thank you, Peter! That worked!

 

[mcomp72]

Actually, one further question:

If I select more than one row, only the top-most row in the selection changes font color. Is there a way to change the formula so the font color in ALL the rows selected will turn red?

 

[Peter_SSs]

Assuming that column Z is available as a helper, see if this is any good.

CF Rule is

=AND(ROW()>=Z$1,ROW()<=Z$2)

with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range("Z1").Value = Selection.Row
  Range("Z2").Value = Selection.Row + Selection.Rows.Count - 1
End Sub