Formulas in Conditional Formula

[magmike]

The field (E$5) that will determine the color of the row (E1:E15), is a zipcode and there are 98 poosible entries for this. To avoid having 98 rules (the 98 zip codes are divided up between 6 people). I am trying to use VLOOKUP to determine the color (6 total colors, so 6 rules), but am having problems.

I am using this without luck:

VLOOKUP(E$5,Territories,4,FALSE)="Territory1"

When I use this, it converts it to this:

="VLOOKUP(E$5,Territories,4,FALSE)=""Territory1""

Any ideas or suggestions on how to do this through VB?

Thanks in advance for your help,

magmike

 

[Claus Busch]

Hi Mike,

Am Fri, 26 Dec 2014 07:30:30 -0800 (PST) schrieb magmike:

The field (E$5) that will determine the color of the row (E1:E15), is a zip code and there are 98 poosible entries for this. To avoid having 98 rules (the 98 zip codes are divided up between 6 people). I am trying to use VLOOKUP to determine the color (6 total colors, so 6 rules), but am having problems.

I am using this without luck:

VLOOKUP(E$5,Territories,4,FALSE)="Territory1"

please upload your file and post us the link.


Regards
Claus Busch

 

[magmike]

Hi Mike,

Am Fri, 26 Dec 2014 07:30:30 -0800 (PST) schrieb magmike:


please upload your file and post us the link.


Regards
Claus Busch

https://www.dropbox.com/s/cfz9emcqnbmmbnd/Tracker.xlsm?dl=0

 

[magmike]

https://www.dropbox.com/s/cfz9emcqnbmmbnd/Tracker.xlsm?dl=0

Tracker tab. E5:O5 is the conditional format.

 

[Claus Busch]

Hi Mike,

Am Fri, 26 Dec 2014 08:22:18 -0800 (PST) schrieb magmike:

Tracker tab. E5:O5 is the conditional format.

I am not at my PC. I wrote you a mail to invite you for the Tracker
file.


Regards
Claus Busch

 

[Claus Busch]

Hi Mike,

Am Fri, 26 Dec 2014 18:06:46 +0100 schrieb Claus Busch:

I wrote you a mail to invite you for the Tracker

or try it in Tracker sheet code module in this way:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E")) Is Nothing _
Or Target.Count > 1 Then Exit Sub

With WorksheetFunction
If .CountIf(Sheets("Territories").Range("A1:A20000"), Target) > 0
Then
Range("E" & Target.Row & ":O" & Target.Row) _
.Interior.Color = .Index(Sheets("Territories") _
.Range("Territories"), .Match(Target, Sheets("Territories")
_
.Range("A1:A20000"), 0), 1).Interior.Color
Else
Range("E" & Target.Row & ":O" & Target.Row) _
.Interior.Color = xlNone
End If
End With
End Sub

You have to color all entries in column A in sheet Territories


Regards
Claus Busch