Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > conditional formatting with vba
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,355
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting Excel Learner Excel Worksheet Functions 1 March 6th 07 08:32 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 3 January 20th 07 02:02 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 0 January 15th 07 04:35 PM
Conditional Formatting... ChrisMattock Excel Worksheet Functions 11 June 28th 06 09:04 PM
Conditional Formatting that will display conditional data BrainFart Excel Worksheet Functions 1 September 13th 05 05:45 PM


All times are GMT +1. The time now is 12:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement