Conditional Formatting: Highlights Cells in Two Columns that are Equal to, or Between Reference Cells

[Zarfot]

I have a data set of road signs that exist along a interstate highway, and the mile point location range from when that style of sign starts, and stops (Columns A & B). I am looking to highlight all cells in Columns A and B that fall within a reference mile point range found in Columns C and D.


For example, if A2 falls between C2 & D2, highlight A2 pink. And, if B2 falls between C2 & D2, highlight B2 pink.

What would be the best formula to accomplish this?

Start Mile Point 0 9 10 10 11 12.9 30.15 31.75 33.35 78.95 78.95 80.95 81.55 84.15 87.45 87.95 91.65 253.15 256.45 340.65 340.95 407.55 410.05 414.6 417 443.85 445.75 457.6 460 668.6 671 756.1 779.1 866.8 869.2 885.7 888.1 890.3 892.7 898.5 1026.5 1032.3 1034.7 1047.1 1049.5 1049.5 1052.5 1052.5 1067.5 1068 1068.5 1071.5 1076.2 1076.2 1080.9 1083.7 1083.7 1103.7 1103.7 1104.7 1109.4 1120.9 1125.6 3050.9 4860.9 4863.3 4867.8 4870.2 5005.6 5008 5011 5013.4 5256.9 5259.3 5260.4 5262.8 5267.4 5269.8 5270.9 5273.3 5611.75 5616.45 5617.45 5619.45 5621.45 5653.45 5655.05 5697.85 5701.75 5703.75 5705.75 5710.25 5723.65 5723.65 5737.05 5737.55 5751.55 5754.55 5754.55 5875.55 5875.55 5959.55 5961.65 6131.75 6133.45 6162.8 6164.4 6682.05 6682.75 7526.35 7531.05 7566.35 7571.05 8081.25 8086.25 8089.05 8091.3 8096 8113.2 8113.7 8113.7 8113.7 8114.2 8117.3 8121.05 8124.15 8126.15 8126.15 8126.15 8128.15 8129.15 8129.15 8130.15 8132.65 8135.35 8137.85 8139.35 8139.35 8140.85 8145.55 8146.05 8147.35 8147.35 8147.35 8148.65 8150.65 8150.65 8150.65 8176.65 8177.25 8194.8 8448 8450.8 8458.8 8458.8 8466.8 8469.6 8470.6 8471.6 8472.6 8474.3 8475.9 8479.9 8491 8492.5 8493.7 8494.2 8496.7 8515.7 8516.2 8599.7 8600.2 8648.7 8651.2 8684.8 8685.1 8748.7 8749.2 8957.3 8958.6 8960.3 8961.6 8967.3 8968.6 8970.3 8971.6 9135.3 9135.6 9192.3 9192.7 9321.4 9321.7 9370.9 9371.2 9454.9 9455.3 9948.5 9949.8 9953.5 9954.8 9960.5 9961.8 9965.5 9966.8 10854.9 10855.4 10919 10921.4 10943.7 10943.7 10945.7 10947.1 10949.1 10949.1 11361.1 11361.4 11463.4 11538.2 11538.6 11618.2 11620.7 12070.1 12070.9 12163.4 12163.7 12407.5 12836.5 12838.6 12870.4 12870.7 12875.1 12876 13043.6 13043.6 13043.6 13064.6 13066.7 13156 13157.4 13391.5 13393.9 13394.2 13538.6 13541.1 14303.6 14928.5 14929.2 14935.6 14938.1 15056.7 15059.1 15449.6 15507.2 15509.7 15900.2 15902.7 15904.9 15906.1 15908.1 16167 16372 16380 16383.1 16411.1 16412.7 16473.2 16474.4 16475.4 16477 16477.5 16479.1 16480.3 16481.3 16482.3 16484 16487 16487.5 16489.5 16492.2 16492.7 16494.4 16496.4 16496.9 16499.9 16501.6 16503.3 16503.3 16511.3 16513.1 16531.9 16532.6 17242.6 17243.6 17245.3 17246.3 17248 17249 17251.2 17252.2 17253.9 17254.9 17256.6 17257.6 17258.6 17258.6 17259.6 17261.1 17561.1 17563.6 17564.9 17580.6 17581.9 17583 17584.3 17587.4 17587.8 17600.8 17603.2 17607 17608.8 17611.3 17626.8 17629.3 17871.8 17871.8 17993 18272 22169 23003 23143 23870 23951.5 23951.5 23954 23954 24046.4 24047.7 24055.4 24056.7 24531 24722.8 24725.3 24892.8 24905.7 24907 24909.7 24911 24919.7 24921 24923.7 24925 24942.1 24942.7 25410.4 25410.6 25461.5 25461.7 25801.2 25802.5 25804.5 25805.8 25836.7 25838 25839.4 25841.9 25901.1 26094.1 26153.3 26154.2 26529.2 26529.6 26710.1 26941.5 26942.2 27241.2 27242.5 27243.8 27578.1 27578.3 27643.1 27643.4 27712.1 27712.4 27793.1 27793.4 27833.2 27833.4 28011.7 28014.2 28093.2 28093.5 28186.2 28186.6 28214.3 28214.6 28254.3 28254.7 28293.6 28294.9 28296.9 28298.2 28302.2 28303.5 28305.5 28306.8 28345.7 28348.2 28387.1 28388.4 28390.4 28391.7 28395.7 28397 28399 28400.3 28577.9 28578.7 28960.6 28961.3 29152.5 29152.7 29242.6 29242.8 29470.6 29470.9 29696.4 29697.5 29846.3 29848 29868.4 29870.5 29950.9 29953.3 29960.6 29963.1 29965 29965.8 29967.4 29995.2 29996.4 30003.2 30004.5 30006.9 30006.9 30008.2 30009.5 30040.2 30041.5 30064.2 30065.5 30073.6 30074.9 30076.1 30076.1 30077.5 30078.8 30082.5 30083.8 30414.3 30416.8 30424.3 30426.8 30444.9 30446.2 30453.9 30455.2 30464.6 30465.5 30466.1 30478.9 30480.8 30609.7 30612.2 30874.1 30876.6 31019.6 31022.1 31503.5 31506 32100.9 32103.4 32441.5 32442.8 32456.6 32462 32463.2 32618.6 32656 32657.3 32917.8 32917.8 33178.3 33178.3 33289.8 33289.8 33401.8 33401.8 33550.8 33552.1 33612.2 33613.5 33620.7 33626.4 33632 33632.8 33646.2 33648.7 33734.3 33736.1 33784 33786.5 33874.5 33874.5 33974.8 33975.8 34002.4 34003.7 34030.3 34031 34035.3 34036 34139.1 34140.2 34151.1 34152.2 34153.8 34154.6 34155.6 34199.3 34201.8 34204.3 34236.5 34405.5 34411.5 34413.9 34523.3 34523.3 34523.3 34552.7 34554 34560.7 34562 34615.7 34617 34625.7 34627 35095.7 35097 35098.3 35305.8 35307.1 35370.8 35372.1 36262.8 36334.8 36336.1 36407.8 36409.1 37141.3 37143.6 37172.3 37174.6 38372.2 38374.7 38674.7 38674.7 38674.7 38732.9 38732.9 38734.6 38736.4 38739.2 38739.2 38739.8 38743 38744.6 38757.2 38758.4 38763.4 38764.6 38776.6 38777.8 38779.9 38781.1 39018.5 39018.9 39023.1 39025.9 39028.5 39340.9 39343.2 39343.9 39773.9 39774.6 39825.4 39827.8 40002.3 40002.3 40004.8 40005.5 40006.5 40006.5 40007 40007 40007.5 40008.5 40009 40009 40009.5 40009.5 40010.5 40012.3 40014.8 40048.6 40050.6 40469.2 40470 40719.9 40720.1 41527.6 41528.4 41530.9 41533.1 41846.4 41847.7 41894.4 41895.7 42005.8 42006.4 42705 42705.3 42790.9 42793.4 42794.4 42894.4 42896.9 42980.5 42980.9 43180.1 43181.4 43182.7 43243.8 43246.3 43553.8 43556.3 43798.8 43801.3 44354.4 44355.7 44449.4 44450.7 44774.3 44775.8 45528.8 45531.3 45813.8 45816.3 45850.8 46149.1 46150.8 46152.5 46316 46316.6 46476.4 46478.7 46541.8 46542.4 47041.6 47042.6 47096.1 47097.2 47917.7 47920.1 48080.7 48083.2 48359.3 48359.6 48733.3 49049.2 49049.4 50137.5 50139.2 50279.1 50279.7 50394.3 50394.6 50810.3 50810.6 50918.9 50920.2 50925.2 50926.5 50928.5 51161.5 51171.5 51316.5 51420.5 51564.5 52140.5 52142 52143.7 52146.2 52154.5 52155.8 52312.8 52314.1 52321.4 52323.9 52325.9 52327.4 52519.4 53243 53243 53244.5 53246.3 53247.3 53247.3 53248.7 53251 53254 53256 53259 53261 53282.4 53285.4 53357.5 53360.5 53381.3 53383.3 53386.3 53388.3 53391.3 53393.6 53394.8 53394.8 53396 53397.8 53399 53399 53468.2 53468.2 54348.2 54355.8 54357.1 54362.8 54364.1 54655.1 54655.1 55126 55126 55618.1 55619.4 55627.1 55628.4 55631.9 56483.9 56483.9 56906.9 58243.9 58243.9 59570.9 59570.9 59937.9 61137.9 61137.9 61147.9 61147.9 61761.4 61761.4 61886.4 61886.4 62537.9 62577.9 62577.9 62622.4 63522.4 63524 63534 63534 63662 63662.8 64072.8 64073.8 64075.1 64093 64094.3 64144.1 64145.4 64160.2 64161.5 64189.5 64190.8 64191.8 64193.1 64212.9 65993.3 66005.3 66477.3 66477.3 67377.8 67377.8 67916.3 68535.4 69231.1 69455.4 69455.4 70393.4 70403.4 70522.4 70532.4 70799.4 70800.4 70801.4 70802.4 70808.4 70848.4 70851.4 70852.4 70853.4 70854.4 70880.4 70881.4 70928.4 70929.4 70969.4 70970.4 71017.4 71018.4 71058.4 71188.4 71191.4 71246.4 71247.4 71250.4 71251.4 72301.4 72301.4 72764.1 72764.1 72764.6 72766.3 72766.8 72766.8 72796.8 72798.1 72817.7 72819 72819.5 72819.5 72819.5 72820 72820.3 72820.3 72820.3 72820.6 72820.8 <colgroup><col></colgroup><tbody> </tbody>	End Mile Point 9 10 10 11 12.9 30.15 31.75 33.35 78.95 78.95 80.95 81.55 84.15 87.45 87.95 91.65 253.15 256.45 340.65 340.95 407.55 410.05 414.6 417 443.85 445.75 457.6 460 668.6 671 756.1 779.1 866.8 869.2 885.7 888.1 890.3 892.7 898.5 1026.5 1032.3 1034.7 1047.1 1049.5 1049.5 1052.5 1052.5 1067.5 1068 1068.5 1071.5 1076.2 1076.2 1080.9 1083.7 1083.7 1103.7 1103.7 1104.7 1109.4 1120.9 1125.6 3050.9 4860.9 4863.3 4867.8 4870.2 5005.6 5008 5011 5013.4 5256.9 5259.3 5260.4 5262.8 5267.4 5269.8 5270.9 5273.3 5611.75 5616.45 5617.45 5619.45 5621.45 5653.45 5655.05 5697.85 5701.75 5703.75 5705.75 5710.25 5723.65 5723.65 5737.05 5737.55 5751.55 5754.55 5754.55 5875.55 5875.55 5959.55 5961.65 6131.75 6133.45 6162.8 6164.4 6682.05 6682.75 7526.35 7531.05 7566.35 7571.05 8081.25 8086.25 8089.05 8091.3 8096 8113.2 8113.7 8113.7 8113.7 8114.2 8117.3 8121.05 8124.15 8126.15 8126.15 8126.15 8128.15 8129.15 8129.15 8130.15 8132.65 8135.35 8137.85 8139.35 8139.35 8140.85 8145.55 8146.05 8147.35 8147.35 8147.35 8148.65 8150.65 8150.65 8150.65 8176.65 8177.25 8194.8 8198.7 8450.8 8458.8 8458.8 8466.8 8469.6 8470.6 8471.6 8472.6 8474.3 8475.9 8479.9 8491 8492.5 8493.7 8494.2 8496.7 8515.7 8516.2 8599.7 8600.2 8648.7 8651.2 8684.8 8685.1 8748.7 8749.2 8957.3 8958.6 8960.3 8961.6 8967.3 8968.6 8970.3 8971.6 9135.3 9135.6 9192.3 9192.7 9321.4 9321.7 9370.9 9371.2 9454.9 9455.3 9948.5 9949.8 9953.5 9954.8 9960.5 9961.8 9965.5 9966.8 10854.9 10855.4 10919 10921.4 10943.7 10943.7 10945.7 10947.1 10949.1 10949.1 11361.1 11361.4 11463.4 11538.2 11538.6 11618.2 11620.7 12070.1 12070.9 12163.4 12163.7 12407.5 12836.5 12838.6 12870.4 12870.7 12875.1 12876 13043.6 13043.6 13043.6 13064.6 13066.7 13156 13157.4 13391.5 13393.9 13394.2 13538.6 13541.1 14303.6 14928.5 14929.2 14935.6 14938.1 15056.7 15059.1 15449.6 15507.2 15509.7 15900.2 15902.7 15904.9 15906.1 15908.1 16167 16372 16380 16383.1 16411.1 16412.7 16473.2 16474.4 16475.4 16477 16477.5 16479.1 16480.3 16481.3 16482.3 16484 16487 16487.5 16489.5 16492.2 16492.7 16494.4 16496.4 16496.9 16499.9 16501.6 16503.3 16503.3 16511.3 16513.1 16531.9 16532.6 17242.6 17243.6 17245.3 17246.3 17248 17249 17251.2 17252.2 17253.9 17254.9 17256.6 17257.6 17258.6 17258.6 17259.6 17261.1 17561.1 17563.6 17564.9 17580.6 17581.9 17583 17584.3 17587.4 17587.8 17600.8 17603.2 17607 17608.8 17611.3 17626.8 17629.3 17871.8 17871.8 17993 18272 22169 23003 23143 23870 23951.5 23951.5 23954 23954 24046.4 24047.7 24055.4 24056.7 24531 24722.8 24725.3 24892.8 24905.7 24907 24909.7 24911 24919.7 24921 24923.7 24925 24942.1 24942.7 25410.4 25410.6 25461.5 25461.7 25801.2 25802.5 25804.5 25805.8 25836.7 25838 25839.4 25841.9 25901.1 26094.1 26153.3 26154.2 26529.2 26529.6 26710.1 26941.5 26942.2 27241.2 27242.5 27243.8 27578.1 27578.3 27643.1 27643.4 27712.1 27712.4 27793.1 27793.4 27833.2 27833.4 28011.7 28014.2 28093.2 28093.5 28186.2 28186.6 28214.3 28214.6 28254.3 28254.7 28293.6 28294.9 28296.9 28298.2 28302.2 28303.5 28305.5 28306.8 28345.7 28348.2 28387.1 28388.4 28390.4 28391.7 28395.7 28397 28399 28400.3 28577.9 28578.7 28960.6 28961.3 29152.5 29152.7 29242.6 29242.8 29470.6 29470.9 29696.4 29697.5 29846.3 29848 29868.4 29870.5 29950.9 29953.3 29960.6 29963.1 29965 29965.8 29967.4 29995.2 29996.4 30003.2 30004.5 30006.9 30006.9 30008.2 30009.5 30040.2 30041.5 30064.2 30065.5 30073.6 30074.9 30076.1 30076.1 30077.5 30078.8 30082.5 30083.8 30414.3 30416.8 30424.3 30426.8 30444.9 30446.2 30453.9 30455.2 30464.6 30465.5 30466.1 30478.9 30480.8 30609.7 30612.2 30874.1 30876.6 31019.6 31022.1 31503.5 31506 32100.9 32103.4 32441.5 32442.8 32456.6 32462 32463.2 32618.6 32656 32657.3 32917.8 32917.8 33178.3 33178.3 33289.8 33289.8 33401.8 33401.8 33550.8 33552.1 33612.2 33613.5 33620.7 33626.4 33632 33632.8 33646.2 33648.7 33734.3 33736.1 33784 33786.5 33874.5 33874.5 33974.8 33975.8 34002.4 34003.7 34030.3 34031 34035.3 34036 34139.1 34140.2 34151.1 34152.2 34153.8 34154.6 34155.6 34199.3 34201.8 34204.3 34236.5 34405.5 34411.5 34413.9 34523.3 34523.3 34523.3 34552.7 34554 34560.7 34562 34615.7 34617 34625.7 34627 35095.7 35097 35098.3 35305.8 35307.1 35370.8 35372.1 36262.8 36334.8 36336.1 36407.8 36409.1 37141.3 37143.6 37172.3 37174.6 38372.2 38374.7 38674.7 38674.7 38674.7 38732.9 38732.9 38734.6 38736.4 38739.2 38739.2 38739.8 38743 38744.6 38757.2 38758.4 38763.4 38764.6 38776.6 38777.8 38779.9 38781.1 39018.5 39018.9 39023.1 39025.9 39028.5 39340.9 39343.2 39343.9 39773.9 39774.6 39825.4 39827.8 40002.3 40002.3 40004.8 40005.5 40006.5 40006.5 40007 40007 40007.5 40008.5 40009 40009 40009.5 40009.5 40010.5 40012.3 40014.8 40048.6 40050.6 40469.2 40470 40719.9 40720.1 41527.6 41528.4 41530.9 41533.1 41846.4 41847.7 41894.4 41895.7 42005.8 42006.4 42705 42705.3 42790.9 42793.4 42794.4 42894.4 42896.9 42980.5 42980.9 43180.1 43181.4 43182.7 43243.8 43246.3 43553.8 43556.3 43798.8 43801.3 44354.4 44355.7 44449.4 44450.7 44774.3 44775.8 45528.8 45531.3 45813.8 45816.3 45850.8 46149.1 46150.8 46152.5 46316 46316.6 46476.4 46478.7 46541.8 46542.4 47041.6 47042.6 47096.1 47097.2 47917.7 47920.1 48080.7 48083.2 48359.3 48359.6 48733.3 49049.2 49049.4 50137.5 50139.2 50279.1 50279.7 50394.3 50394.6 50810.3 50810.6 50918.9 50920.2 50925.2 50926.5 50928.5 51161.5 51171.5 51316.5 51420.5 51564.5 52140.5 52142 52143.7 52146.2 52154.5 52155.8 52312.8 52314.1 52321.4 52323.9 52325.9 52327.4 52519.4 53243 53243 53244.5 53246.3 53247.3 53247.3 53248.7 53251 53254 53256 53259 53261 53282.4 53285.4 53357.5 53360.5 53381.3 53383.3 53386.3 53388.3 53391.3 53393.6 53394.8 53394.8 53396 53397.8 53399 53399 53468.2 53468.2 54348.2 54355.8 54357.1 54362.8 54364.1 54655.1 54655.1 55126 55126 55618.1 55619.4 55627.1 55628.4 55631.9 56483.9 56483.9 56906.9 58243.9 58243.9 59570.9 59570.9 59937.9 61137.9 61137.9 61147.9 61147.9 61761.4 61761.4 61886.4 61886.4 62537.9 62577.9 62577.9 62622.4 63522.4 63524 63534 63534 63662 63662.8 64072.8 64073.8 64075.1 64093 64094.3 64144.1 64145.4 64160.2 64161.5 64189.5 64190.8 64191.8 64193.1 64212.9 65993.3 66005.3 66477.3 66477.3 67377.8 67377.8 67916.3 68535.4 69231.1 69455.4 69455.4 70393.4 70403.4 70522.4 70532.4 70799.4 70800.4 70801.4 70802.4 70808.4 70848.4 70851.4 70852.4 70853.4 70854.4 70880.4 70881.4 70928.4 70929.4 70969.4 70970.4 71017.4 71018.4 71058.4 71188.4 71191.4 71246.4 71247.4 71250.4 71251.4 72301.4 72301.4 72764.1 72764.1 72764.6 72766.3 72766.8 72766.8 72796.8 72798.1 72817.7 72819 72819.5 72819.5 72819.5 72820 72820.3 72820.3 72820.3 72820.6 72820.8 72821.8 <colgroup><col></colgroup><tbody> </tbody>	Reference Start Mile Point 0.04 0.38 80.95 81.55 84.15 87.45 87.95 91.29 91.65 253.15 457.6 1067.5 5715.38 5718.31 5718.51 5721.01 5722.71 5754.84 8067.51 8072.51 8075.31 8077.56 8082.26 8098.96 8099.46 8099.96 8100.46 8100.96 8103.56 8107.31 8110.41 8111.41 8112.41 8113.41 8114.41 8447.99 8450.8 8484.39 8491 8492.5 8496.7 8536.94 9944.8 12511.22 13389.6 13391.2 13392.41 14134.33 14137.14 17246.5 17247.64 17251.85 17257.25 17403.82 17559.25 17597.38 17599 17600.19 17605.2 17605.44 17607 17616.26 17619.07 17894.45 22031.2 23661.12 23959.43 24528.28 24531.09 24891 25941.94 25944.75 26790.39 29689.12 29965.2 30004.65 30005.9 30061.9 30073.8 30075.2 30080.2 30412 30462.3 30463.2 32454.25 32637.43 32637.44 32640.24 33607.68 33624 33972.4 34086.07 34153.2 34196.9 34199.4 34200.89 34212.93 34391.68 34409.1 34416.28 34419.09 34520.08 34568.44 38649.81 38734.71 38755.7 39049.8 40528.25 40753.31 41261.51 42827.66 42835.45 42838.26 43437.64 43978.23 44886.78 45966.49 46337.22 46509.61 46641.63 47872.59 47872.6 48450.72 48605.25 50346.96 50486.16 50605.87 50792.99 50821.81 50889.66 50892.47 50991.51 51081.63 51224.51 51234.51 51313.05 51313.54 51369.45 51379.51 51440.46 51440.96 51483.51 51611.41 51611.9 51627.51 51635.79 51638.6 52203.51 52387.41 69476.69 69479.5 69479.66 69479.94 69482.47 70384.49 70394.49 70513.49 70523.49 <colgroup><col></colgroup><tbody> </tbody>	Reference End Mile Point 0.38 80.95 81.55 84.15 87.45 87.95 91.29 91.65 253.15 457.6 1067.5 4940.87 5718.31 5718.51 5721.01 5722.71 5754.84 8067.51 8072.51 8075.31 8077.56 8082.26 8098.96 8099.46 8099.96 8100.46 8100.96 8103.56 8107.31 8110.41 8111.41 8112.41 8113.41 8114.41 8166.53 8450.8 8484.39 8491 8492.5 8496.7 8536.94 8875.63 11086.87 13389.6 13391.2 13392.41 14134.33 14137.14 15261.44 17247.64 17251.85 17257.25 17403.82 17559.25 17597.38 17599 17600.19 17605.2 17605.44 17607 17616.26 17619.07 17894.45 17897.26 23661.12 23959.43 24528.28 24531.09 24891 25941.94 25944.75 26790.39 26793.2 29965.2 30004.65 30005.9 30061.9 30073.8 30075.2 30080.2 30412 30462.3 30463.2 32454.25 32637.43 32637.44 32640.24 32640.25 33624 33972.4 34086.07 34153.2 34196.9 34199.4 34200.89 34212.93 34391.68 34409.1 34416.28 34419.09 34520.08 34568.44 38649.81 38734.71 38755.7 39049.8 39337.63 40753.31 41261.51 42827.66 42835.45 42838.26 43437.64 43440.45 44886.78 44889.59 46337.22 46509.61 46641.63 46644.44 47872.6 48450.72 48605.25 48754.77 50486.16 50605.87 50792.99 50821.81 50889.66 50892.47 50991.51 51081.63 51224.51 51234.51 51313.05 51313.54 51369.45 51379.51 51440.46 51440.96 51483.51 51611.41 51611.9 51627.51 51635.79 51638.6 52203.51 52387.41 52539.22 69479.5 69479.66 69479.94 69482.47 70384.49 70394.49 70513.49 70523.49 70789.17 <colgroup><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

	A	B	C	D
1	Start mile point	End Mile Point	Reference Start Mile Point	Reference End Mile Point
2	0	9	0.04	0.38
3	9	10	0.38	80.95
4	10	10	80.95	81.55
5	10	11	81.55	84.15
6	11	12.9	84.15	87.45
7	12.9	30.15	87.45	87.95
8	30.15	31.75	87.95	91.29

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Select columns A:B. Click Conditional Formatting > New Rule > Use a Formula, and enter:
=AND(A1<>"",A1>=$C1,A1<=$D1)

Click Format... and choose a fill color.

 

[Zarfot]

Hi Eric,

Thanks for the help. Is there a way to make it so more than just that one row is highlighted? I'm looking to have all of A & B be run against the reference data in C&D. About 90% of the values in A&B should end up highlighted.

For example: Just in that table you shared, A2-A8/B2-B8 should all be highlighted as they're within the mile point range of C3-D3.

Any ideas?

 

[Eric W]

Same instructions as above, use this formula:

=(A1<>"")*(SUMPRODUCT(--(A1>=$C$2:$C$10),--(A1<=$D$2:$D$10),--ISNUMBER($C$2:$C$10),--ISNUMBER($D$2:$D$10)))

changing the C and D ranges to match your sheet.

Upon thinking about it, I'm not entirely sure if that's what you want. This version checks to see if both values in A:B are in the C:D range:

=(A1<>"")*(SUMPRODUCT(--($A1>=$C$2:$C$10),--($B1<=$D$2:$D$10),--ISNUMBER($C$2:$C$10),--ISNUMBER($D$2:$D$10)))

 

[Zarfot]

Eric,

You're fantastic!!!!! The first formula you provided is EXACTLY what I needed.

Thank you so much!

 

[Eric W]

Glad to help!