conditional formatting with vba

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Barb Reinhardt said:
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
Click to expand...
 
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??
 
Back
Top