Change range of cell colour by clicking a single cell on same row

[Asouf]

I have been trying for hours at various VBA codes but havnt found a specific formula that works.

My team have a form that we use to generate work instruction which is basically merged cells with instructions prewritten that they highlight a number of cells ( A1 to A8 ) and then 'fill colour' in yellow. As simple as that..

Is there a formula to change the colour of cells A1 to A8 to yellow by clicking only on A1

Then repeat this for B1 - B8 through to M1 - M8 etc etc

Thanks in advance

 

[Mentor82]

I have been trying for hours at various VBA codes but havnt found a specific formula that works.

My team have a form that we use to generate work instruction which is basically merged cells with instructions prewritten that they highlight a number of cells ( A1 to A8 ) and then 'fill colour' in yellow. As simple as that..

Is there a formula to change the colour of cells A1 to A8 to yellow by clicking only on A1

Then repeat this for B1 - B8 through to M1 - M8 etc etc

Thanks in advance

Hi,
You cannot colour a cell using formula based on clicking in the cell. However, it possible to do it with VBA code. Place below code in the worksheet module - paste iy in the worksheet module you want to have the cells coloured. Here I set a range of from A1 to Z1. Let me know it that's what you need.

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
Dim rng as range
Set rng = range("A1:Z1")
If Not Intersect(Target, rng) Is Nothing Then
    Range(rng, range(rng).offset(7,0)).interior.color=vbyellow
End if
Set rng = nothing

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

If you select any cell in Row(1) The below 8 rows will have interior color Yellow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  1/28/2019  1:32:34 PM  EST
If Target.Row = 1 Then
If Target.CountLarge > 1 Then Exit Sub
Target.Resize(8).Interior.ColorIndex = 6
End If
End Sub

 

[Asouf]

This worked perfectly however, I made a huge error in the description of what it is I wanted.

I need to click A19 for example to colour cells A19 to I19 (9 cells on a single row, not a single column)

A19 to I19 through to A36 to I36

Apologies..

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

If you select any cell in Row(1) The below 8 rows will have interior color Yellow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  1/28/2019  1:32:34 PM  EST
If Target.Row = 1 Then
If Target.CountLarge > 1 Then Exit Sub
Target.Resize(8).Interior.ColorIndex = 6
End If
End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  1/29/2019  3:25:06 AM  EST
If Target.Column = 1 Then
If Target.CountLarge > 1 Then Exit Sub
Target.Resize(, 9).Interior.ColorIndex = 6
End If
End Sub

 

[Asouf]

100% perfect, apart from an undo function would be great for when a misclick on a row requires it to go back to white..

Im new to VBA and find it really fascinating....

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  1/29/2019  3:25:06 AM  EST
If Target.Column = 1 Then
If Target.CountLarge > 1 Then Exit Sub
Target.Resize(, 9).Interior.ColorIndex = 6
End If
End Sub

 

[My Aswer Is This]

Try this:

If you double click on the cell in column A the color will be removed.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  1/29/2019  3:38:56 AM  EST
If Target.Column = 1 Then
Cancel = True
Target.Resize(, 9).Interior.ColorIndex = xlNone
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  1/29/2019  3:25:06 AM  EST
If Target.Column = 1 Then
If Target.CountLarge > 1 Then Exit Sub
Target.Resize(, 9).Interior.ColorIndex = 6
End If
End Sub

 

[Asouf]

Thank you so much... this works as intended...

Try this:

If you double click on the cell in column A the color will be removed.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  1/29/2019  3:38:56 AM  EST
If Target.Column = 1 Then
Cancel = True
Target.Resize(, 9).Interior.ColorIndex = xlNone
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  1/29/2019  3:25:06 AM  EST
If Target.Column = 1 Then
If Target.CountLarge > 1 Then Exit Sub
Target.Resize(, 9).Interior.ColorIndex = 6
End If
End Sub

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank you so much... this works as intended...