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.

 

[Rick Rothstein]

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.

Here is some event code that does what you asked for, but there is a "flaw" in what you asked for. Selecting a cell in Columns K:N will color cells in Columns A:J of the row click on either green, yellow, red or gray. The "flaw" is if someone accidentally clicks a cell in Columns K:N by mistake (say they pick the wrong row), you have left no way to clear the mistaken color. Anyway, here is the code...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column > 10 And Target.Column < 15 And Target.Count = 1 Then
     With Intersect(Target.EntireRow, Columns("A:J"))
       .Cells.Interior.Color = Choose(Target.Column - 10, vbGreen, vbYellow, vbRed, 12632256)
       .Cells.Font.Color = Choose(Target.Column - 10, vbBlack, vbBlack, vbWhite, vbBlack)
      End With
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[My Aswer Is This]

We need specific details.
Like in what column do plan to enter the rating and what columns on that row do you want colored.
And when you say color do you mean interior cell color. And when you say "Box" do you mean "cell"

You said "by simply selecting the appropriate box"

I do not know what "Box" means.

And you would need to enter the rating in the cell. Just selecting a cell would not tell us what color to change the cell to.

 

[My Aswer Is This]

I guess Rick got a lot more details looking at the link. I never click on links.

 

[Rick Rothstein]

I guess Rick got a lot more details looking at the link. I never click on links.

Not a whole lot more... I still had to make some guesses at information that was missing (column designations, for example).

 

[asdsparky]

Let's see if I can clarify this a bit...if I click in cell G14, I need A14:J14 to fill with green. If I select cell H14, I need the same range of cells to change to Yellow. Same concept with I14 and J14 and their corresponding colors. I need the same function to apply to each row individually through row 400. I currently use a similar worksheet that someone else created but I am unable to reverse engineer the method they used to create this function. There is nothing in the VBA code that applies (that I can find).

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. I tried copying the information after the name and adding it to the string below the existing code but it didn't work for me. Also, I don't follow your reference to columns K:N. Those columns shouldn't have any data. You also mentioned clearing the fields. I have a "reset form" that should (if all code is correct) clear the user entered form data. I will reply with a screenshot of my code view also, so you can look it over. Thanks for the help.

[/URL]******** async src="//embedr.flickr.com/assets/client-code.js" charset="utf-8">*********>[/IMG]

 

[asdsparky]

...and sorry for the lack of info. I didn't realize I left all of that off of the screenshot. Also, I embedded the jpg this time instead of a link.

 

[MARK858]

Maybe someone at EF will gleam more from the link posted in the thread there (if their securi firewall lets you see it via the link ).

 

[asdsparky]

Maybe someone at EF will gleam more from the link posted in the thread there (if their securi firewall lets you see it via the link ).

I apologize for missing the cross post rule. I am new to these forums and admit I failed to comply. I would prefer that someone remind me of my indiscretion rather than call me out like that. I came her for legitimate assistance and I appreciate those who have spent their time trying to help rather than spending their time looking for accidental rule breakers.

 

[asdsparky]

Rick, here are the current codes. Again, I apologize for the lack of information in the original post.

[/URL]******** async src="//embedr.flickr.com/assets/client-code.js" charset="utf-8">*********>[/IMG]