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.

 

[asdsparky]

Thanks. I'll give that a shot on a test worksheet.

 

[MARK858]

Bad advice on my last post as you were only testing there if the range contained a value.
Better example with the last code you posted...

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = "$F$7" Then
     If Target.Value = "" Then
       Rows("13:400").EntireRow.Hidden = False
     If Application.Trim(Target.Value) = "Dorm" Then
       Rows("13:24,68:175,221:400").EntireRow.Hidden = False
       Rows("25:67,176:200").EntireRow.Hidden = True
     ElseIf Application.Trim(Target.Value) = "Food Service" Then
       Rows("13:24,68:175,186:400").EntireRow.Hidden = False
       Rows("25:67,176:185").EntireRow.Hidden = True
     End If
   End If
End Sub

 

[asdsparky]

That is essentially what I ended up using. I had to play with the wording and structuring to make it work with a single selection and row first. Then I was able to add more rows and drop list selections to the mix.

 

[asdsparky]

Here is my code modified to do all of the changes you have requested above (you can modify the colors using the assignments at the beginning of the code)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Green As Long, Yellow As Long, Red As Long, Gray As Long, Black As Long, White As Long
  Green = RGB(0, 255, 0)
  Yellow = RGB(255, 255, 0)
  Red = RGB(255, 0, 0)
  Gray = RGB(128, 128, 128)
  Black = RGB(0, 0, 0)
  White = RGB(255, 255, 255)
  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"))
        Intersect(.Cells, Columns("G:J")).ClearContents
        Target.Value = "X"
        Target.HorizontalAlignment = xlCenter
        .Cells.Interior.Color = Choose(Target.Column - 6, Green, Yellow, Red, Gray)
        .Cells.Font.Color = Choose(Target.Column - 6, Black, Black, White, White)
        .Cells.Font.Bold = Target.Column > 8
      End With
    End If
  End If
  '
  '  Put any other Worksheet_SelectionChange code here
  '
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

I've added a new level of complexity to this request...I was wondering if I can add a line to discriminate against rows with bold text. I tried adding

If range ("B13:B300").Font.Bold = False Then
' before the line:
If Target.Row > 13  'etc, etc...

I also tried

If range ("B13:B300").Font.Bold = true Then End Sub

Neither of these accomplished the task (and yes, I used the End If at the end). Any ideas? Thanks in advance.

 

[Rick Rothstein]

I've added a new level of complexity to this request...I was wondering if I can add a line to discriminate against rows with bold text. I tried adding

If range ("B13:B300").Font.Bold = False Then
' before the line:
If Target.Row > 13  'etc, etc...

I also tried

If range ("B13:B300").Font.Bold = true Then End Sub

Neither of these accomplished the task (and yes, I used the End If at the end). Any ideas? Thanks in advance.

(Untested) Try this line of code instead (and don't for the End If to close of the code block this new If statement creates)...

If Target.Font.Bold = False Then

 

[asdsparky]

(Untested) Try this line of code instead (and don't for the End If to close of the code block this new If statement creates)...

If Target.Font.Bold = False Then

No change. I assume it's because my column target range is >6 and <11 and the bold font exists in column 2 (B). I have a sample of the worksheet here: https://www.mrexcel.com/forum/excel...ber-column-while-ignoring-rows-bold-text.html if it helps visualize the setup. The sample is missing columns 7-10 which are the selection areas that change the row colors.

 

[asdsparky]

Hey Rick, I figured it out. Adding this first line (red) worked. I needed "entirerow" rather than "range" or simply "row". Thank you for all of your input. You have helped me learn a tremendous amount in a short time. You Rock!

 [COLOR=#ff0000]If Target.EntireRow.Font.Bold = False Then[/COLOR]
  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"))
        Intersect(.Cells, Columns("G:J")).ClearContents
        Target.Value = Chr(252)
        Target.Font.Name = "Wingdings"
        Target.Font.Size = 10
        Target.HorizontalAlignment = xlCenter
         .Cells.Interior.Color = Choose(Target.Column - 6, Green, Red, Gray, Clear)
         .Cells.Font.Color = Choose(Target.Column - 6, Black, Black, Black, Black)
        End With
    End If
   End If
  End If