I would like a VBA code to change a row (from A to G) to a spacific color based on the text if column C17:C200.
For example if the cell says "Concern" the row turns yellow.
here is the code i'm using now. the problem is when i change the text in row C to anything, it turns the row to gray (15). none of the colors work?
i cant use conditional formatting because i also like this sheet to a pie chart which changes colors based on the cell color.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C1:C250")) Is Nothing Then Exit Sub
Select Case Cells(Target.Row, "A").Value
Case "Concern"
Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Interior.ColorIndex = 6
Case "Critical"
Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Interior.ColorIndex = 3
Case "Complete"
Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Interior.ColorIndex = 4
Case "Open"
Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Interior.ColorIndex = 2
Case ""
Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Interior.ColorIndex = xlNone
Case Else
Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Interior.ColorIndex = 15
End Select
End Sub
Bookmarks