Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 15th, 2008, 01:16 AM
Registered User
 
Join Date: Mar 2008
Location: Smyrna, Tennessee, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Cell Coloring in VBA based on value in column

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.

 
Old March 15th, 2008, 06:54 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 15th, 2008, 06:16 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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.
 
Old March 24th, 2008, 03:29 PM
Authorized User
 
Join Date: Mar 2008
Location: Athens, , Greece.
Posts: 35
Thanks: 0
Thanked 1 Time in 1 Post
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Get GridView Cell Value Based on GridView Column stublair C# 2008 aka C# 3.0 0 September 4th, 2008 08:30 AM
copy columns based on cell values EricB123 Excel VBA 0 December 18th, 2007 12:51 AM
Coloring the chart plot area based on x-axis scale simranjeev Reporting Services 1 October 25th, 2007 02:50 PM
How to create a Hyperlink based on Cell Contents chatguy Excel VBA 4 October 15th, 2007 09:43 PM
change background color of cell based on value vurtman ASP.NET 1.0 and 1.1 Basics 4 February 26th, 2004 03:52 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.