Change background colour of 1st five cells (Column A to E) of the selected row on selection of any cell in the row

[amsaini15]

Hello everyone
It would be great if someone can assist with VBA to fill in a 1st 5 cells of the selected row with color when I select the any cell in that row. It can be any light colour as I can change it to fit later.
Once I select a different cell in a different row, previous colour should be restored and new 1st 5 cells should be coloured (if its different row)

e.g. If G13 is selected, A13 to E13 should be filled with some light color. Then if I select H6, A13 to E13 colour should be restored and A6 to E6 should be coloured.

This is to make it easy to see 5 first cells of the selected row every time I click on any cell

Thank you.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Select any cell in A:E.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:E")) Is Nothing Then Exit Sub
    ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
    Range("A" & Target.Row).Resize(, 5).Interior.ColorIndex = 6
End Sub

 

[amsaini15]

If Intersect(Target, Range("A:E")) Is Nothing Then Exit Sub ActiveSheet.UsedRange.Interior.ColorIndex = xlNone Range("A" & Target.Row).Resize(, 5).Interior.ColorIndex = 6

Thanks @mumps .

It works well. The only issue is it is clearing background color from all other cells in the sheet. I only want to change color and restore of these 5 cells only. Background color of the remaining cells in other columns should remain as it is. Possible? Thanks

 

[mumps]

This version assumes that you have data in column A when you select a cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:E")) Is Nothing Then Exit Sub
    Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 5).Interior.ColorIndex = xlNone
    Range("A" & Target.Row).Resize(, 5).Interior.ColorIndex = 6
End Sub

 

[Peter_SSs]

Here is another method to try

This method will not destroy any existing colour in the worksheet, including columns A:E. Another advantage is that if the worksheet already has conditional formatting, this new CF will temporarily over-ride it so the selected row is clearly visible. (If other CF is added after the CF described here, it may hinder this & a slight adjustment could be needed. Post back if further information about this is required)

1. Select the whole of columns A:E

2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("row")=ROW() -> Format... -> On the Fill tab select the colour you want** -> Ok -> Ok

3. Right click the sheet name tab and choose 'View Code'

4. Copy and Paste the code below into the main right hand pane that opens at step 4.

5. Close the Visual Basic window & test.

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

** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab if you go to 'More Colors...' it would be easy to make a colour unique for your sheet.

A further advantage of this method it that even with the vba code involved, Excel's 'Undo' function is preserved.

 

[amsaini15]

This version assumes that you have data in column A when you select a cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:E")) Is Nothing Then Exit Sub
    Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 5).Interior.ColorIndex = xlNone
    Range("A" & Target.Row).Resize(, 5).Interior.ColorIndex = 6
End Sub

Thanks @mumps

Now it doesn't clear background colour of whole sheet but it still loses background color of the cell in first 5 columns.
Lets say if C4 background color is Red and I click on F4, the first 5 cells of the row have background color Yellow as expected.
When I click on F6 now expectation is that C4 background colour will not be lost (should be Red again) when (A6:E6) are Yellow now as expected.

 

[amsaini15]

Here is another method to try

This method will not destroy any existing colour in the worksheet, including columns A:E. Another advantage is that if the worksheet already has conditional formatting, this new CF will temporarily over-ride it so the selected row is clearly visible. (If other CF is added after the CF described here, it may hinder this & a slight adjustment could be needed. Post back if further information about this is required)

1. Select the whole of columns A:E

2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("row")=ROW() -> Format... -> On the Fill tab select the colour you want** -> Ok -> Ok

3. Right click the sheet name tab and choose 'View Code'

4. Copy and Paste the code below into the main right hand pane that opens at step 4.

5. Close the Visual Basic window & test.

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

** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab if you go to 'More Colors...' it would be easy to make a colour unique for your sheet.

A further advantage of this method it that even with the vba code involved, Excel's 'Undo' function is preserved.

Thank you @Peter_SSs
This works exactly as I want. It retains the original background color of the cell when I move away from the row.

If No other alternative, I will certainly use this. The only small issue is that Screen updating slows down moving around spreadsheet. It makes a big difference when you are constantly moving around in a big sheet.

Please advise if it is possible to achieve same outcome without screen updating?

 

[Peter_SSs]

Well, the screen does have to update, otherwise the colour in your selected row would not update.
However, you could try this slight alternative to compare.
With this one you do lose Excel's normal 'Undo' functionality though.

Remove the Conditional Formatting that I proposed last time.
I'm assuming column Z can be used as a helper. Any other unused column would do.

1. Hide column Z
2. Select columns A:E, or as much of them as you want this to apply to.
3. CF -> New rule -> with formula =$Z$1=ROW() -> Format as required
4. Replace the previous Selection_Change code with this one

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range("Z1").Value = ActiveCell.Row
End Sub

 

[amsaini15]

Well, the screen does have to update, otherwise the colour in your selected row would not update.
However, you could try this slight alternative to compare.
With this one you do lose Excel's normal 'Undo' functionality though.

Remove the Conditional Formatting that I proposed last time.
I'm assuming column Z can be used as a helper. Any other unused column would do.

1. Hide column Z
2. Select columns A:E, or as much of them as you want this to apply to.
3. CF -> New rule -> with formula =$Z$1=ROW() -> Format as required
4. Replace the previous Selection_Change code with this one

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range("Z1").Value = ActiveCell.Row
End Sub

Perfect You are a legend @Peter_SSs (Dont care about undo functionality.)

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.