VBA compare with more than one result

**josephrowan** · 08-26-2007, 03:27 AM

i am really just beginning VBA after spending alot of time manipulating it. My problem today, I would hope, is an easy solve.

I am trying to get a compare of the data in column A to do two (or more) things. For now, it is color the cell and a couple to the right and the 2nd part would be to add a category description. The first part is working fine - the part with the color fill. Anytime the case sensitive string ABC is entered, cells in columns A,B,C & D fill green.

It is getting the compare to put the client category in that is not working. Below, is the part that is working.

Ideally the D column cell return a category code, like "cli" for client when ABCxxxx is entered.

_____A________B_________C________D____
1 | abc123 (fill grn) (fill grn) cli
2 | def213 - - -
3 | abc599 (fill grn) (fill grn) cli

I have tried a bunch of stuff, but I am just hacking at it. Anybody care to assist?

thanks

Joe

Here is the Green Fill code.
##############################

Private Sub worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1:a5")) Is Nothing Then _
       Invoices Intersect(Target, Range("a1:a5"))
End Sub

Sub Invoices(ByVal rng As Range)
Dim r As Range, myColor As Integer
For Each r In rng
     myColor = xlNone
     Select Case r.value

     Case "ABC" To "ABC999999"
     myColor = 4
     
     End Select
     r.Resize(, 3).Interior.ColorIndex = myColor
Next
End Sub

##############################

[/FONT][/FONT][/SIZE]

**StephenR** · 08-26-2007, 07:35 AM

I don't follow, where is the client category?

**josephrowan** · 08-26-2007, 11:21 AM

abc is the client, 123 is the job. The "cli" is a shortening to assist in creating a new UID based on. Later in the process, I need to be able to filter similar tasks from different clients.

There are other categories for postage, mailing, design and such, but it will all follow the same rule in the end. All tolled, there are 11 different sub-categories

does that help? Sorry for not being clear enough.

J-

**StephenR** · 08-26-2007, 11:36 AM

Try this. which assumes client code is three characters. Btw Select Case is case-sensitive.

Sub Invoices(ByVal rng As Range)
Dim r As Range, myColor As Integer
For Each r In rng
     myColor = xlNone
     Select Case r.value

     Case "ABC" To "ABC999999"
     myColor = 4
     r.offset(0,3)=left(r,3)
     
     End Select
     r.Resize(, 3).Interior.ColorIndex = myColor
Next
End Sub