Cell Coloring in VBA based on value in column

FinalFive · March 15th, 2008, 01:16 AM

I am having a real tough with this one:
I am wanting to set the back color of an entire row such as A2:F2 based on the value in the "F" column of that row. The data to the right of column F should be left as is.
A column: ID
B Column: Name
C Column: Shift
D Column: Hours
E Column: Percent to Goal
F Column: Rating such as "Outstanding" , "Needs Improvement", etc.based on a simple lookup formula.
The data to the right is a separate performance criteria.

I am tried about twenty different code strategies
Here is what I have currently which results in a crash:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim i As Integer
  Application.ScreenUpdating = False
  Set rng = Range("a2:f250")
  Do Until i = 10
    i = 2
   With rng
     If Cells(i, 6).Value = "Outstanding" Then
        Range(" & i & ", 6).Interior.ColorIndex = 4
     ElseIf Cells(i, 6).Value = "Meets Expectations" Then
        Range(" & i & ", 6).Interior.ColorIndex = 31
     ElseIf Cells(i, 6).Value = "Exceeds Expectations" Then
        Range(" & i & ", 6).Interior.ColorIndex = 50
     ElseIf Cells(i, 6).Value = "Needs Development" Then
        Range(" & i & ", 6).Interior.ColorIndex = 27
     Else
         Range(" & i & ", 6).Interior.ColorIndex = 3
     End If
   End With
i = i + 1
Loop
End Sub
Your help is appreciated as I am trying to learn a new skill.

vemaju · March 15th, 2008, 06:54 AM

Hi,
You should change the code like this
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim i As Integer
  Application.ScreenUpdating = False
  Set rng = Range("a2:f250")
    i = 2
    Do Until i = 10
        With rng
            If Cells(i, 6).Value = "Outstanding" Then
               Cells(i, 6).Interior.ColorIndex = 4
            ElseIf Cells(i, 6).Value = "Meets Expectations" Then
               Cells(i, 6).Interior.ColorIndex = 31
            ElseIf Cells(i, 6).Value = "Exceeds Expectations" Then
               Cells(i, 6).Interior.ColorIndex = 50
            ElseIf Cells(i, 6).Value = "Needs Development" Then
               Cells(i, 6).Interior.ColorIndex = 27
            Else
                Cells(i, 6).Interior.ColorIndex = 3
            End If
        End With
        i = i + 1
    Loop
  Application.ScreenUpdating = True
End Sub

ps. I would change the if clause with select case

-vemaju

stanleydgromjr · March 15th, 2008, 06:16 PM

FinalFive,

Here is another approach with Select Case.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Right click the sheet tab you want the code in, and click on View Code. Paste the code there.

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2:F250")) Is Nothing Then Exit Sub
    Select Case Cells(Target.Row, "F").Value
        Case "Outstanding"
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 4
        Case "Meets Expectations"
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 31
        Case "Exceeds Expectations"
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 50
        Case "Needs Development"
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 27
        Case ""
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = xlNone
        Case Else
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 3
    End Select
End Sub

Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

tstav · March 24th, 2008, 03:29 PM

Quote:

quote:Originally posted by FinalFive
F Column: Rating such as "Outstanding" , "Needs Improvement", etc.based on a simple lookup formula.

Hi FinalFive,
From the above I understand that while you are working in some other Sheets (not in the one you want colored, let's call it Sht1) a lookup formula keeps the F column of that Sht1 updated according to whatever changes you may be doing elsewhere.
So, maybe what you want is: when you go to Sheet Sht1 you want to see color depending on the values of the cells of column F.
If my assumptions so far are correct, then here's what you can do.
Put the following in the Worksheet_Activate Event of the Sheet you want colored.
Whenever you tab into this Sheet, colors will get updated.

Code:

Private Sub Worksheet_Activate()
Dim rng As Range, cell As Range
Set rng = Range("A2:A250")
For Each cell In rng
   Select Case cell.Offset(0, 5).Value
       Case "Outstanding"
           cell.Resize(1, 6).Interior.ColorIndex = 4
       Case "Meets Expectations"
           cell.Resize(1, 6).Interior.ColorIndex = 31
       Case "Exceeds Expectations"
           cell.Resize(1, 6).Interior.ColorIndex = 50
       Case "Needs Development"
           cell.Resize(1, 6).Interior.ColorIndex = 27
       Case Else
           cell.Resize(1, 6).Interior.ColorIndex = 3
   End Select
Next
End Sub

Regards, tstav