Macro not selecting correct cells to change color 1

[vba317]

I have written a macro that I use on excel 2003. I am trying to change the color of specific cells in a certain range. What is happening is instead of changing the color of the cell the cell's color stays the same and I get a False instead of the value that is supposed to be in the cell.

Tom

'Set the color of high priority clients to red
    For Each f In Worksheets("ClientProcessing").Range("A9:A" & intLastRow)
    If f = "MSP" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "SPN" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "UCS" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "BFP" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "TFS" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "BRA" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "CAR" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "CHI" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "APP" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "INR" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "MLT" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "NBA" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "ROR" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "MIA" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "NDI" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "PVR" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "TRA" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    If f = "VRA" Then f.Offset(0, 0) = ActiveCell.Interior.ColorIndex = 3
    Next

 

[SkipVought]

hi,

    For Each f In Worksheets("ClientProcessing").Range("A9:A" & intLastRow)
        If f = "MSP" Then f.Interior.ColorIndex = 3
        If f = "SPN" Then f.Interior.ColorIndex = 3
        If f = "UCS" Then f.Interior.ColorIndex = 3
        If f = "BFP" Then f.Interior.ColorIndex = 3
        If f = "TFS" Then f.Interior.ColorIndex = 3
        If f = "BRA" Then f.Interior.ColorIndex = 3
        If f = "CAR" Then f.Interior.ColorIndex = 3
        If f = "CHI" Then f.Interior.ColorIndex = 3
        If f = "APP" Then f.Interior.ColorIndex = 3
        If f = "INR" Then f.Interior.ColorIndex = 3
        If f = "MLT" Then f.Interior.ColorIndex = 3
        If f = "NBA" Then f.Interior.ColorIndex = 3
        If f = "ROR" Then f.Interior.ColorIndex = 3
        If f = "MIA" Then f.Interior.ColorIndex = 3
        If f = "NDI" Then f.Interior.ColorIndex = 3
        If f = "PVR" Then f.Interior.ColorIndex = 3
        If f = "TRA" Then f.Interior.ColorIndex = 3
        If f = "VRA" Then f.Interior.ColorIndex = 3
    Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vba317]

Thanks Skip, it worked great!

 

[SkipVought]

Should have benn posted in forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

how about this...

    For Each f In Worksheets("ClientProcessing").Range("A9:A" & intLastRow)
        Select Case f.Value
            Case "MSP", "SPN", "UCS", "BFP", "TFS", "BRA", "CAR", "CHI", "APP", "INR", "MLT", "NBA", "ROR", "MIA", "NDI", "PVR", "TRA", "VRA"
                f.Interior.ColorIndex = 3
        End Select
    Next

And finally, I'd suggest keeping a LIST on a sheet and either use a loop within this loop or a lookup to match the f.value. It is much better to have data in a list than buried in code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

In fact I'd go a step further and suggest doing it ALL on the sheet via Conditional Foamatting.

Make a list
[tt]
MyList
MSP
SPN
UCS
BFP
TFS
BRA
CAR
CHI
APP
INR
MLT
NBA
ROR
MIA
NDI
PVR
TRA
VRA
[/tt]

Use the heading as a rangename

Select the cells to be formatted, where I assume from your code that A9 is the first cell in the selection ...
[tt]
=Match(A9,MyList,0)>0
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vba317]

If I did the list method where would I put the color designation?

 

[PHV]

In the condtional formatting dialog box.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886