Results 1 to 10 of 10

VBA Code to change row color

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    VBA Code to change row color

    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
    Last edited by arlu1201; 04-05-2013 at 10:29 AM. Reason: Use code tags in future.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1