ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   conditional formatting with vba (https://www.excelbanter.com/excel-worksheet-functions/150940-conditional-formatting-vba.html)

David G[_2_]
conditional formatting with vba
 
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.xlColorIndexA utomatic

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

Barb Reinhardt
conditional formatting with vba
 
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

"David G" wrote:

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.xlColorIndexA utomatic

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

David G[_2_]
conditional formatting with vba
 


"Barb Reinhardt" wrote:

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

"David G" wrote:

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.xlColorIndexA utomatic

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

David G[_2_]
conditional formatting with vba
 
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??

"Barb Reinhardt" wrote:

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

"David G" wrote:

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.xlColorIndexA utomatic

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


All times are GMT +1. The time now is 06:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com