Conditional Formatting based on map coordinates

[Transport77]

I'm in logistics...I have several warehouses within a few miles of each other and I have a Excel spreadsheet detailing trailers supposedly parked at each location. This spreadsheet details the trailers' current/most recent GPS coordinates...latitude in one cell column and longitude in the next cell column. I am attempting to use conditional formatting to highlight where each trailer is located based on the coordinates listed (so I don't have to search each one). So, if a warehouse is between a particular latitude range and longitude range, I'd like the given GPS coordinates (both the latitude and longitude cells on the row) to be highlighted if they fall within the ranges for a particular warehouse. However, some of my warehouses have either overlapping latitudes or longitudes. So while the latitude may highlight correctly, the longitude may not because the trailer's longitude coordinate is within that overlap, and vice versa. I'm trying to figure out a formula rule for each warehouse location to basically say:

* For the latitude column..."if the latitude is between this range and the longitude is between this range, then highlight the latitude this color."
* For the longitude column..."if the longitude is between this range and the latitude is between this range, then highlight the longitude this color."
* so that both coordinates are highlighted the same to match the color designated by the location.

How would I properly write these rules and apply them both the latitude and longitude columns? Any help would be greatly appreciated!

 

[Kerryx]

Could you please supply some dummy data ( with the overlaps ) that can be used as we don't know how you lat & long values are displayed in your columns
e.g

34°28'N
42.546245 1.601554

 

[Transport77]

The GPS coordinates are reported as just a number to hundred thousandths decimal (6), latitude in one column and latitude in the next column. I have plotted out each building location to basically their property lines, thus providing their designated latitude and longitude ranges. So, to provide an example...

Bldg A has a latitude range of 10.000000 to 20.000000 and a longitude range of 10.000000 to 15.000000.

Bldg B has a latitude range of 25.000000 to 30.000000 and a longitude range of 12.000000 to 17.000000.

Bldg C has a latitude range of 27.000000 to 35.000000 and a longitude range of 30.000000 to 40.000000.

As you can see, there is a longitude overlap between Bldgs A & B and a longitude overlap between Bldgs B & C.

If a trailer's GPS coordinates are 17.000000 latitude and 13.000000 longitude, what would the conditional formula be to have the longitude cell reflect that the trailer is at Bldg A?

If another trailer's GPS 28.000000 latitude and 35.000000 longitude, what would the conditional formula be to have the latitude cell reflect that the trailer is at Bldg C?

 

[Kerryx]

Something like this

Book1
	A	B	C	D	E	F
1	Bldg	LatMn	LatMx	LongMn	LongMx
2	A	10.000000	20.000000	10.000000	15.000000
3	B	25.000000	30.000000	12.000000	17.000000
4	C	27.000000	35.000000	30.000000	40.000000
5
6	Trailer	Lat	Long
7	D	33.000000	37.000000
8	E	25.000000	14.000000
9	F	26.000000	13.000000
10	G	28.000000	32.000000
11	H	20.000000	40.000000
12	I	15.000000	10.000000
13	J	15.000000	21.000000
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A7:A13	Expression	=AND($B7>=$B$2,$B7<=$C$2,$C7>=$D$2,$C7<=$E$2)	text	NO
A7:A13	Expression	=AND($B7>=$B$3,$B7<=$C$3,$C7>=$D$3,$C7<=$E$3)	text	NO
A7:A13	Expression	=AND($B7>=$B$4,$B7<=$C$4,$C7>=$D$4,$C7<=$E$4)	text	NO

 

[Transport77]

I figured it had something to do with greater/less than. Thank you!

 

[Kerryx]

ok glad to help