Need to color code rows by column data

[LizzyAnn]

So I have a massive database query that (eventually) turns itself into a single spreadsheet with about 6 columns or so. I need to color code eah row based off of the data in one of the columns. There are only about 9 variables in that column, (thus far), so I can't use conditional formatting. However, I have no idea how to go about this in VB, any help would be appreciated.

 

[LizzyAnn]

Takes me to row 1042.

 

[SkipVought]

What is row 1042, with reaspect to the data in the table?

In the middle of the table?

At the END of the table

Past the end of the table?

Help me out here!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

last row.

 

[SkipVought]

Then I do not understand what is causing the problem.

Please post your LIST.

Also please post 10 - 15 rows of your table, starting at the top.

I will construct a test.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

LIST:
Sharod James
Falco Benfield
Chad Markle
Kevin Brown
Lynn Thompson
Phil Arnold
Dean DeSantis
Edward Friel
Rick Miller


I'm not sure I can post the first few rows.. they contain personal data and all that junk.

 

[SkipVought]

copy a few rows of the table to another sheet.

Replace sensitive data with dummy data.

Send me a copy of that.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also please post the actual code that you are running.

Also What is your list Range Name?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Okie dokie.
I just realized that sometimes some of the other columns are left blank.
Dunno if that makes much of a difference.


(A)
Address
403 W. Pine Street
(BLANK)
701 E. Pratt St.
1501 S. Clinton Street
8100 Cryden Way.
17 Stenersen Lane
51 Monroe Street
(BLANK)
(BLANK)
400 E Pratt St. STE 910
1875 Connecticut Ave., NW

(B)
City
Athens
(BLANK)
Baltimore
Baltimore
Forestville, MD
Baltimore
Rockville
(BLANK)
(BLANK)
Balt. MD
Washington

(C)
Company Name
BlankFace Industries
I love the 80s
I love the 90s
ANTM
ABDC
Global Construction
Word Domination
Ancient Calculator
Bad Credit Union
I know my ABCs
Can Count to 3
Ace of Spades

(D)
Customer Name
Robert Wendall
Rob Griffin
Joe Schmoe
Carlyle Blue
Katherine Green
Dora the Explorer
Bah Nah-Nah
Monkey Butz
Buster
Pitman
Holi
Frankenstein

(E)
Phone Number
713-867-5309
202-583-8500
410-614-8500
1-800-694-8565
202-163-9350
410-109-4639
1-800-405-1120
1-800-203-4185
410-612-6555
301-839-7222
301-255-8879
410-161-5256

(F)
Sales Rep
Sharod James
Falco Benfield
Chad Markle
Sharod James
Chad Markle
Sharod James
Sharod James
Falco Benfield
Kevin Brown
Chad Markle
Lynn Thompson
Falco Benfield

(G)
Zip
18810
(BLANK)
(BLANK)
21224
20747
21030
20850
(BLANK)
(BLANK)
21202

 

[SkipVought]

It appears that you do not have data for atleast one of the names in your list.

This should solve that problem and a couple of other minor ones as well.

Sub Color()
'
' Color Macro
' Macro recorded 2/4/2009 by EWatson
'

'
    Dim r As Range, rng As Range
    
    With ActiveSheet.UsedRange
        'set the range of names in your table
        Set rng = Range([F2], [F2].End(xlDown))
        
        'make sure the AutoFilter is on
        If Not .Parent.AutoFilterMode Then .Parent.[A1].AutoFilter
    End With
    
    For Each r In [MyList]  '[b]substitute YOUR LIST NAME in the brackets[/b]

        'filter each name in the list
        Range("A1").AutoFilter _
            Field:=6, Criteria1:=r.Value

        
        'shade the visible cells if there are any other than the heading row
        If rng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
            With rng.SpecialCells(xlCellTypeVisible).Interior
                .ColorIndex = r.Interior.ColorIndex
                .Pattern = xlSolid
            End With
        End If
    Next

    'shade the headings
    With Range("A1:G1").Interior
        .ColorIndex = 38
        .Pattern = xlSolid
    End With
    
    'show all the data in the table
    ActiveSheet.ShowAllData
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Well, by the 1042nd row, everyone's had at least one row to their name.

 

[LizzyAnn]

1004 Error, "No cells were found"

highlights this:
If rng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then

 

[SkipVought]

Change that statement to this...

If rng.currentregion.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Well, this time the header row colored itself.

Oh well. I've just been informed that the prez no longer wants to do this (go fig.)
That tends to happen a lot around here.

Anyway, thanks for all your help, especially with setting up the queries and whatnot. I'm sure I'll be able to use that for something else later on.

(And sorry this took up so much of your time.)

 

[SkipVought]

No problem.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}