conditional formatting with vba

[Guest]

I have a spreadsheet with project managers assigned to different projects and
I need to assign each project manger and the respective row to a certain
color. the code I used is
Sub PROJ_MANAGER()

If Target.Column <> 6 Then
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False
Select Case LCase(Target.EntireRow)
Case "MOL"
Target.EntireRow.Interior.ColorIndex = 34
Case "TGB"
Target.EntireRow.Interior.ColorIndex = 36
Case "DGC"
Target.EntireRow.Interior.ColorIndex = 35
Case "JHS"
Target.EntireRow.Interior.ColorIndex = 37
Case "CDB"
Target.EntireRow.Interior.ColorIndex = 38
Case "JJB"
Target.EntireRow.Interior.ColorIndex = 39
Case "WHM"
Target.EntireRow.Interior.ColorIndex = 40
Case "JLK"
Target.EntireRow.Interior.ColorIndex = 41
Case "SMC"
Target.EntireRow.Interior.ColorIndex = 42
Case "AB"
Target.EntireRow.Interior.ColorIndex = 43
Case "REP"
Target.EntireRow.Interior.ColorIndex = 44
Case "JEM"
Target.EntireRow.Interior.ColorIndex = 45
Case "EWT"
Target.EntireRow.Interior.ColorIndex = 44
Case Else
Target.EntireRow.Interior.ColorIndex.xlColorIndexAutomatic

End Select
Application.EnableEvents = True 'should be part of Change macro
End Sub

My data looks like this:
1156. MOL
1248. TGB
1356. DGC
1482. JHS
1524. CDB
1565. JHS
1568. MDG
1610. JJB
1679. DGC

ANY HELP WOULD BE GREAT

 

[Guest]

It looks like you have most of it. I'd put this in a worksheet change event

Right click on the sheet tab that would be affected.

Paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, ActiveSheet.Column("B")) Is Nothing Then


End If
End Sub

This assumes that your identifying names/initials are in column B

Paste the rest of your code inside the If/End if

HTH,
Barb Reinhardt

 

[Guest]

It looks like you have most of it. I'd put this in a worksheet change event

Right click on the sheet tab that would be affected.

Paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, ActiveSheet.Column("B")) Is Nothing Then


End If
End Sub

This assumes that your identifying names/initials are in column B

Paste the rest of your code inside the If/End if

HTH,
Barb Reinhardt

 

[Guest]

barb-
i tried what you said and it comes up with an error that states "object does
not support this property or method." how do i fix this??