I have a prospect database that has over 1000 prospects in it. For each contact there will 5 opportuntities to contact and disposition (each representing one column; Columns L,N,P,R,T). Each of these coumns has a dropdown box that allows for 13 different selections. In column V, I have the following formula: =IF(T2>1,T2,IF(R2>1,R2,IF(P2>1,P2,IF(N2>1,N2,IF(L2>1,L2,"None"))))) to find out what the last disposition is (the one in the farthest right column). It is this column that I need to base the conditional formatting on. I have the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("v:v"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "APPT SCHEDULED"
fc = 1: fb = False: bc = 22
Case "CALL BACK"
fc = 1: fb = False: bc = 40
Case "LEFT MESSAGE"
fc = 1: fb = False: bc = 4
Case "DNC REGISTRY"
fc = 2: fb = False: bc = 3
Case "NO CONTACT"
fc = 2: fb = False: bc = 29
Case "ADT CUSTOMER"
fc = 2: fb = False: bc = 25
Case "BAD NUMBER"
fc = 2: fb = False: bc = 1
Case "NOT INTERESTED"
fc = 2: fb = False: bc = 30
Case "COMPETITOR"
fc = 2: fb = False: bc = 46
Case "PROPOSED"
fc = 2: fb = False: bc = 18
Case "SOLD"
fc = 2: fb = False: bc = 50
Case "MAILER"
fc = 1: fb = False: bc = 39
Case "VISIT"
fc = 2: fb = False: bc = 16
Case Else
fc = 1: fb = False: bc = xlNone
End Select
With Cells(c.Row, 1).Resize(, 22)
.Font.ColorIndex = fc
.Font.Bold = fb
.Interior.ColorIndex = bc
End With
Next
End Sub
This will highlight the columns that I need based on the V column. The problem that I am having is when the value in V changes, the formatting of that row does not change and when there is a new value in V, there is no formatting unless I autofill V down. The formatting needs to change whenever V changes.
Also, when Columns L,N,P,R,or T change, I need to place the date that they changed in the next cell. This date will need to stay there and not change next time the sheet is opened. I accomplished this with the following formula:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L2:L10000,N2:N10000,P2:P10000,R2:R10000,T2:T10000")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
However, I cannot get the two programs to play happily together. I need to include both of these in this worksheet. i have not be able to accomplish this. I am using Excel 2003. Any help would be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("v:v"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "APPT SCHEDULED"
fc = 1: fb = False: bc = 22
Case "CALL BACK"
fc = 1: fb = False: bc = 40
Case "LEFT MESSAGE"
fc = 1: fb = False: bc = 4
Case "DNC REGISTRY"
fc = 2: fb = False: bc = 3
Case "NO CONTACT"
fc = 2: fb = False: bc = 29
Case "ADT CUSTOMER"
fc = 2: fb = False: bc = 25
Case "BAD NUMBER"
fc = 2: fb = False: bc = 1
Case "NOT INTERESTED"
fc = 2: fb = False: bc = 30
Case "COMPETITOR"
fc = 2: fb = False: bc = 46
Case "PROPOSED"
fc = 2: fb = False: bc = 18
Case "SOLD"
fc = 2: fb = False: bc = 50
Case "MAILER"
fc = 1: fb = False: bc = 39
Case "VISIT"
fc = 2: fb = False: bc = 16
Case Else
fc = 1: fb = False: bc = xlNone
End Select
With Cells(c.Row, 1).Resize(, 22)
.Font.ColorIndex = fc
.Font.Bold = fb
.Interior.ColorIndex = bc
End With
Next
End Sub
This will highlight the columns that I need based on the V column. The problem that I am having is when the value in V changes, the formatting of that row does not change and when there is a new value in V, there is no formatting unless I autofill V down. The formatting needs to change whenever V changes.
Also, when Columns L,N,P,R,or T change, I need to place the date that they changed in the next cell. This date will need to stay there and not change next time the sheet is opened. I accomplished this with the following formula:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L2:L10000,N2:N10000,P2:P10000,R2:R10000,T2:T10000")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
However, I cannot get the two programs to play happily together. I need to include both of these in this worksheet. i have not be able to accomplish this. I am using Excel 2003. Any help would be greatly appreciated.
