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]

They're both on the same sheet.

 

[SkipVought]

at the point of this error, please describe what has happened on the sheet. Is the AutoFilter hiding rows and what rows are visible?

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

 

[LizzyAnn]

All rows are visible. I had some of the columns hidden though.

 

[SkipVought]

However, the macro is HIDING rows, via the AutoFilter. So why are ALL rows visible?

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

 

[LizzyAnn]

Is there a reason they wouldn't be visible??

 

[SkipVought]

What I am saying is that when that statement executes, there should be some rows HIDDEN by the AutoFilter.

If no row are hidden, then there is some other problem.

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

 

[LizzyAnn]

Oh..
Didn't know that part.

 

[SkipVought]

1) did you color-code your list of names -- the one that is your Named Range?

2) what happens when you run the procedure?

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

 

[LizzyAnn]

OK, so now I have the list of names colored,
and now it hides all the rows except the ones w/o a name in the column...

 

[SkipVought]

"...except the ones w/o a name in the column..."

How do the names get in the column? Is that not a result of the Query? So why no names?

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

 

[SkipVought]

BTW...

THAT is the problem!!!

I expect there to be a name on every row in the table in my procedure.

When there is not, TILT!!!

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

 

[LizzyAnn]

I expect there to be a name on every row.... that confuses me/

 

[LizzyAnn]

Ok, so I found the cells missing data and put the names in...
But now.. no data found error... on this line:

With rng.SpecialCells(xlCellTypeVisible).Interior

 

[SkipVought]

And what cells a visible?

What CRITERIA is applied to the AutoFilter column?

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

 

[LizzyAnn]

Well, now all the cells are visible again...

define what you mean by "Criteria

 

[SkipVought]

In the query results column that contains your names, hit your AutoFilter button and select (Custom...)

What do you see?



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

 

[LizzyAnn]

Nothing. All blank.

 

[SkipVought]

Is your heading for this field in F1?

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

 

[LizzyAnn]

Yes.
Column F is the one where the names are from the data, and I is where the "DataValueList" is.

 

[SkipVought]

and if you

select cell F1

hit the [END] key

hit the [DOWN ARROW] key

tell me what happens.

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