Code to change row (cell range) color with or without button

[asdsparky]

I'm working on a spreadsheet that uses colors for quality of items (Good=green, Fair=yellow, Poor=red or N/A=gray). Each row will need to change to the color of the rating for that row and needs to stay within columns A-J. Also, if I select a different rating for the row, the color needs to change for that rating. I would prefer to accomplish this by simply selecting the appropriate box for the row rather than adding a radial button in each box (very time consuming). I have attempted with conditional formatting and with vba codes and with grouping radial buttons but I can't seem to get it right. Here is an example of my worksheet:https://flic.kr/p/ZBjhvq. Any help would be greatly appreciated.

 

[My Aswer Is This]

In your image I see 10 different colors in same row. How would this happen.

 

[My Aswer Is This]

If I understand what you want then this would work:
We will get back to explaining how you can have two selection change events in the same sheet after we get this script working for you by itself.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 7 Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Interior.ColorIndex = 4
If Target.Column = 8 Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Interior.ColorIndex = 6
If Target.Column = 9 Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Interior.ColorIndex = 3
If Target.Column = 10 Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Interior.ColorIndex = 15
End Sub

 

[asdsparky]

Row 13 colors were manually selected. The new color function will apply to row 14 and down.

 

[asdsparky]

Thanks for the response. I will give this a shot tomorrow and let you know.

 

[My Aswer Is This]

My script start in row(1). Try it and if it works I can modify it to start on row (14) I did not see that requirement in your original post.

 

[My Aswer Is This]

Try this:
It will start on row(14)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row < 14 Then Exit Sub
If Target.Column = 7 Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Interior.ColorIndex = 4
If Target.Column = 8 Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Interior.ColorIndex = 6
If Target.Column = 9 Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Interior.ColorIndex = 3
If Target.Column = 10 Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "J")).Interior.ColorIndex = 15
End Sub

 

[Rick Rothstein]

Rick, With the code you provided, I already have a code with this name "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" and I'm getting the ambiguous name error.

You can only have one Worksheet_SelectionChange event procedure per worksheet. Here is what I propose you use for the request in this thread... put any existing and future code you may have for this event after the code I have written in the location I highlighted in green below.

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Target.Row > 13 And Target.Column > 6 And Target.Column < 11 And Target.Count = 1 Then
      With Intersect(Target.EntireRow, Columns("A:J"))
        .Cells.Interior.ColorIndex = Choose(Target.Column - 6, 4, 6, 3, 15)
        .Cells.Font.ColorIndex = Choose(Target.Column - 6, 1, 1, 2, 1)
        .Cells.Font.Bold = Target.Column = 9
      End With
    End If
  End If
[B][COLOR="#008000"]  '
  '  Put any other Worksheet_SelectionChange code here
  '
[/COLOR][/B]End Sub[/td]
[/tr]
[/table]

 

[asdsparky]

Thank you both for the response. Both codes give me the same result. You guys are awesome. I do have a couple of other thoughts on this one: 1. can I add an X the cell with the cell selection at the same time as changing the color? 2. Can I use an RGB code (##,##,##) for custom colors rather than color index? 3. Can I add to the 'Reset_Click' (Module 2) code to clear the fill color when I reset the worksheet?

 

[asdsparky]

Ignore #3 of my previous. I figured out the reset code for the fill color.

 

[asdsparky]

Sub Reset_Click()
Worksheets("Master Indicator List").range("F4:G9").ClearContents
Worksheets("Master Indicator List").range("F14:J400").ClearContents
With range("A14:J400").Interior
.Pattern = xlNone
End With
End Sub