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.

 

[SkipVought]

Hi,

Gonna have you record a macro and post back the recorded code in order to customize. First try going thru the steps without the recorder, in order to become famoliar with the process.

Turn on your macro recorder.

Select ALL of the data in the table

Turn on the AutoFilter Data > Filter > AutoFilter

Select one of the data values in the column with the data you want to color by.

Select the Background (interior) color for that data value.

Select the HEADINGS in row 1.

Select the Background (interior) color for headings.

Turn off your macro recorder.

Make a new list of the unique data values from the column of data. Data > Filter > advanced filter - Copy to another location AND UNIQUE values only Lable this list something like DataValueList and NAME the range with that Name.

Post your recorded code.




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

 

[LizzyAnn]

Ok.. I follow you up until naming the range.. only because I've never done it before.

 

[SkipVought]

faq68-1331.

Also Excel HELP on Name with respect to cells & ranges.

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

 

[SkipVought]

This Excel HELP is particularly good, as it references several naming techinques.

I like the OFFSET method referenced in the FAQ I posted as it dynamically adjusts to the size of the list if your list will change often.

If your list is fairly static, then doing the naming manually might be adequate.

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

 

[LizzyAnn]

Wow, I learned something new today. (seems to be the trend whenever I post here)

Here's the macro:

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

'
Selection.AutoFilter
Range("F2").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("A1:G1").Select
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
End Sub

 

[SkipVought]

What did you name your List or 9 values?

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

 

[SkipVought]

also, you did not macro record...

1. turning on the AutoFilter

2. makeing a selection in the autofilter.

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

 

[SkipVought]

and a small insignificant detail;

what color for each data value?

So you're going to have to...

select all the data cells

turn on your recorder

in turn...

select each value and apply the interior color

until you have selected each one.

turn off your recorder and post back the code.

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

 

[LizzyAnn]

I don't understand how it didnt record turning on the autofilter... but now I have this:

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

'
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="Chad Markle"
Range("F4").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("A1:G1").Select
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
End Sub

 

[LizzyAnn]

Crud, I posted that before I did each one.

 

[SkipVought]

No problem.

Here's an alternative approch, that would really be better.

Color code the names in your LIST. Then I don't need the specific color code values: they will come from your list assignment AND you can change the colors at any time.

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

 

[SkipVought]

Past this code in your module and run.

change [MyList] to Your List Name in this code

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))
    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
        With rng.SpecialCells(xlCellTypeVisible).Interior
            .ColorIndex = r.Interior.ColorIndex
            .Pattern = xlSolid
        End With
    Next

    'shade the headings
    With Range("A1:G1").Interior
        .ColorIndex = 38
        .Pattern = xlSolid
    End With
End Sub

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

 

[LizzyAnn]

I got a 1004 error.

It highlighted this part:
With rng.SpecialCells(xlCellTypeVisible).Interior

and I still owe you the part where I color code each one... I'll do that now before something else happens.

 

[LizzyAnn]

OK, I just changed the font color this time since that's how it is on the other lists.

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

'
Range("F1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="Chad Markle"
Range("F4").Select
Selection.Font.ColorIndex = 3
Selection.AutoFilter Field:=6, Criteria1:="Dean DeSantis"
Range("F25").Select
Selection.Font.ColorIndex = 45
Selection.AutoFilter Field:=6, Criteria1:="Edward Friel"
Range("F43").Select
Selection.Font.ColorIndex = 43
Selection.AutoFilter Field:=6, Criteria1:="Falco Benfield"
Range("F3").Select
Selection.Font.ColorIndex = 50
Selection.AutoFilter Field:=6, Criteria1:="Kevin Brown"
Range("F10").Select
Selection.Font.ColorIndex = 42
Selection.AutoFilter Field:=6, Criteria1:="Lynn Thompson"
Range("F12").Select
Selection.Font.ColorIndex = 41
Selection.AutoFilter Field:=6, Criteria1:="Phil Arnold"
Range("F22").Select
Selection.Font.ColorIndex = 44
Selection.AutoFilter Field:=6, Criteria1:="Rick Miller"
Range("F243").Select
Selection.Font.ColorIndex = 4
Selection.AutoFilter Field:=6, Criteria1:="Sharod James"
Range("F2").Select
Selection.Font.ColorIndex = 33
Range("C1:F1").Select
Range("F1").Activate
Selection.Font.ColorIndex = 40
End Sub

 

[SkipVought]

The code I sent you is based on...

the cell INTERIOR color in Your List.

Do you want the Interior AND the Font to be colored, or one or the other?



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

 

[LizzyAnn]

Just the font,
But the code you posted gets a 1004 error. (whatever that is)

 

[SkipVought]

On what statement?

Hit the Debug Button when you get the error, and the statement will be highlighted.

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

 

[LizzyAnn]

It highlights this:

With rng.SpecialCells(xlCellTypeVisible).Interior

 

[SkipVought]

When you run the macro, are you on the sheet that has your QueryTable on it, the sheet that you are trying to color code the cells?

It seems not to be recognizing the rng object.

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