Solved

Color Row based on cell value

Posted on 2011-09-08
7
237 Views
Last Modified: 2012-05-12
excel 2003
vba routine needed


I need to Loop through all rows
Look in Column A: If a value is  "CS_Project_Number"
Then color that row  "Gray" from  Column A:J

Thanks
fordraiders
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36503285
Does this fit your needs?
Option Explicit

Sub Q27297739()
With ActiveSheet
    If .Cells(1, 1).Value = "CS_Project_Number" Then
        With .Columns("A:J").Interior
            .ColorIndex = 40
            .Pattern = xlSolid
        End With
    End If
End With
End Sub

Open in new window

0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36503299
ColorIndex doesn't have all the colours, but Color can be set using RGB.

Option Explicit

Sub Q27297739()
With ActiveSheet
    If .Cells(1, 1).Value = "CS_Project_Number" Then
        With .Columns("A:J").Interior
            .Color = RGB(127, 127, 127)
            .Pattern = xlSolid
        End With
    End If
End With
End Sub

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 36503390
both solutions color every row  not just the row containing  "CS_Project_Number"

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36503400
Can you give me an example spreadsheet as I've misunderstood your structure.
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 500 total points
ID: 36503463
Is this what you wanted?

Option Explicit

Sub Q27297739()
Dim o_Row As Range

For Each o_Row In ActiveSheet.UsedRange.Rows
    With o_Row
        If .Cells(1, 1).Value = "CS_Project_Number" Then
            With .Columns("A:J").Interior
                .Color = RGB(255, 127, 127)
                .Pattern = xlSolid
            End With
        Else
            .Columns("A:J").Interior.ColorIndex = xlNone
        End If
    End With
Next o_Row
End Sub

Open in new window

0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 36503504
Perfect Thanks !
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36503561
Sorry it took so long.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question