Nesting conditional formatting

C

CoolidgeLow

New Member
Joined
May 30, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Is there a way in Microsoft Excel to nest multiple conditional formating formulas together. I have a formula to create a conditional format, but wanted to apply the same formula to the result to nest conditional formats? I am highlighting highs and lows (via conditional formating) and then want to highlight the results of those identified highs and lows...................

My conditional formate custom formula is:
Highs
=IF(AND(C2>C1,C2>C3),AND(D2>D1,D2>D3)
Lows
=IF(AND(D2<D1,D2<D3),AND(C2<C1,C2<C3)

Market Structure.xlsx
ABCDE
1DateOpenHighLowClose
26/1/2020109.165114.200108.400112.950
36/2/2020113.720114.380108.870110.820
46/3/2020111.000111.350103.900103.960
56/4/2020104.000107.750101.800102.930
66/5/2020103.950104.500100.190104.380
76/8/2020105.530113.020103.970112.950
86/9/2020112.440115.170108.601113.250
96/10/2020114.490116.280110.220113.800
106/11/2020108.690114.920106.240106.950
116/12/2020108.810110.160105.300108.070
126/15/2020107.170107.640103.940107.490
136/16/2020109.810120.850107.700120.810
146/17/2020119.480123.100115.780117.320
156/18/2020117.750129.190117.730127.790
166/19/2020128.170132.170124.750128.500
176/22/2020129.500134.810128.510129.780
186/23/2020131.000132.420127.325127.880
196/24/2020127.130129.929120.420123.350
206/25/2020123.000125.000120.570123.600
216/26/2020124.820126.550120.260122.550
226/29/2020122.400123.209112.110115.050
236/30/2020115.100117.870113.880116.530
247/1/2020117.300129.440116.350128.390
257/2/2020128.470132.500125.195128.650
267/6/2020130.860135.970128.660132.030
277/7/2020131.690135.810129.400130.420
287/8/2020131.880135.140130.700133.760
297/9/2020136.500149.980136.280149.690
307/10/2020147.820159.880147.260153.050
317/13/2020158.990166.184147.420149.060
327/14/2020149.510156.364142.147150.360
337/15/2020150.980155.880147.590154.210
347/16/2020152.720157.280149.480153.310
357/17/2020150.990153.710147.950148.360
367/20/2020148.780151.630146.580150.460
377/21/2020152.780159.060150.220153.360
387/22/2020153.530156.000150.820152.170
397/23/2020152.220158.470148.040150.160
407/24/2020147.244153.100145.170151.170
417/27/2020153.340156.580151.550155.970
427/28/2020154.390155.950148.500148.750
437/29/2020150.000154.420149.200151.570
447/30/2020150.160154.020149.240150.350
457/31/2020153.610158.270151.120154.890
468/3/2020158.360163.420156.620161.820
478/4/2020164.230168.150163.305166.440
488/5/2020164.850169.140163.750165.420
498/6/2020159.570160.500150.050153.870
508/7/2020154.450164.730153.390156.390
518/10/2020157.420157.790147.556150.410
528/11/2020149.280152.250143.210146.410
538/12/2020151.450153.850146.660146.980
548/13/2020148.619152.005146.810149.970
558/14/2020150.850152.500146.200146.850
568/17/2020147.770148.270143.360145.710
578/18/2020147.300152.190146.510149.850
588/19/2020149.000150.890146.173147.370
598/20/2020147.310151.890146.420148.890
608/21/2020148.950150.561146.600147.130
618/24/2020149.500149.700145.600148.580
628/25/2020148.000148.715145.860147.770
638/26/2020151.610167.170151.130164.280
648/27/2020162.590166.150157.860160.970
658/28/2020162.000177.540161.500172.850
668/31/2020172.510175.510168.060173.480
679/1/2020176.997185.440176.630179.270
689/2/2020183.760183.760171.030178.320
699/3/2020172.180176.480165.080166.900
709/4/2020165.000167.420150.380159.910
719/8/2020155.084170.440155.000162.630
729/9/2020166.070166.800159.080161.180
739/10/2020162.240168.600158.933159.440
749/11/2020160.753161.500152.620155.460
759/14/2020157.060160.200155.400157.730
769/15/2020162.715170.440161.770168.520
779/16/2020168.000174.190167.710169.990
789/17/2020165.860168.132161.360164.670
799/18/2020165.232165.780157.540160.470
809/21/2020166.050189.800163.540188.820
819/22/2020187.380195.360182.500193.790
829/23/2020193.490200.730185.000187.250
839/24/2020183.570184.970177.680178.270
849/25/2020182.667186.959176.630182.430
859/28/2020185.000189.010181.660186.290
869/29/2020186.200190.340183.220187.870
879/30/2020187.890193.550185.856188.800
8810/1/2020192.830200.470192.700200.340
8910/2/2020195.370203.560194.800200.000
9010/5/2020201.390204.940197.950204.160
9110/6/2020203.760208.960202.660204.830
9210/7/2020207.950214.260207.500212.550
9310/8/2020221.960228.330219.330223.840
9410/9/2020226.050226.830221.630223.950
9510/12/2020226.203227.000221.350221.680
9610/13/2020221.100239.140219.020238.575
9710/14/2020237.350238.330228.790233.050
9810/15/2020225.000230.066223.650226.470
9910/16/2020227.660231.440222.190222.320
10010/19/2020223.710228.620220.180222.140
10110/20/2020223.630224.980217.230218.400
10210/21/2020218.000227.490216.270222.780
10310/22/2020223.490229.450218.900222.360
10410/23/2020223.300224.880218.500223.960
10510/26/2020223.630229.550216.740218.420
10610/27/2020219.740226.500217.180225.070
10710/28/2020222.000224.960215.640217.400
10810/29/2020219.800221.580214.631217.930
10910/30/2020217.100218.130200.450202.400
11011/2/2020205.660212.800203.710206.880
11111/3/2020208.550209.310196.520203.490
11211/4/2020208.130215.720205.040215.680
11311/5/2020221.975226.590217.120225.060
11411/6/2020235.990255.660228.890253.360
11511/9/2020231.559240.880220.880221.910
11611/10/2020227.840228.200207.500216.980
11711/11/2020220.010232.080217.690231.430
11811/12/2020231.240236.650227.610230.900
11911/13/2020232.590236.440225.000228.100
12011/16/2020222.200232.890220.600232.700
12111/17/2020233.000242.460232.200237.130
12211/18/2020235.880239.189231.500231.990
12311/19/2020236.160257.490235.600255.670
12411/20/2020257.090265.970254.790263.140
12511/23/2020270.000279.500268.600276.580
12611/24/2020279.500284.000268.140273.010
12711/25/2020275.000278.780270.770278.060
12811/27/2020279.700280.600273.010275.340
12911/30/2020275.390293.650274.280293.570
13012/1/2020290.500292.810279.380285.710
13112/2/2020281.440284.532273.790281.330
13212/3/2020281.960292.100281.550289.100
13312/4/2020290.190298.130286.000296.380
13412/7/2020295.650302.618289.000299.980
13512/8/2020299.980307.630297.250306.130
13612/9/2020315.110328.740303.160306.230
13712/10/2020301.000322.090300.070321.990
13812/11/2020321.000331.870316.900330.870
13912/14/2020332.999336.170319.370324.320
14012/15/2020325.410331.100319.010320.140
14112/16/2020321.200327.960320.270325.820
14212/17/2020346.650352.120326.500329.480
14312/18/2020331.980343.600329.480340.710
14412/21/2020344.000358.790341.540354.000
14512/22/2020356.330361.683344.482354.710
14612/23/2020355.000361.040344.210360.560
14712/24/2020360.172363.440355.130356.990
14812/28/2020359.500361.000339.120340.410
14912/29/2020339.960344.940332.050339.570
15012/30/2020339.167344.440333.500338.740
15112/31/2020337.500339.910330.818332.020
1521/4/2021339.980340.000316.460317.900
1531/5/2021320.800335.380320.500335.180
1541/6/2021325.040347.954320.210342.950
1551/7/2021352.010379.390352.000379.290
1561/8/2021391.350402.850387.175399.130
1571/11/2021390.300416.220390.030403.130
1581/12/2021407.900419.750405.560415.290
1591/13/2021419.300423.000404.503408.600
1601/14/2021410.810422.520410.109418.460
1611/15/2021418.000425.990407.090408.300
1621/19/2021420.730438.360420.092434.140
1631/20/2021442.820448.170425.605428.500
1641/21/2021429.310431.000418.886423.640
1651/22/2021422.000429.890418.330422.850
1661/25/2021430.890443.850415.010426.640
1671/26/2021424.980429.390400.310403.400
1681/27/2021394.950420.000383.600402.260
1691/28/2021409.100409.114392.184404.010
1701/29/2021400.200406.590384.030389.030
1712/1/2021395.330416.340393.190416.200
1722/2/2021420.000420.570409.000418.750
1732/3/2021422.495432.610421.875425.070
1742/4/2021427.890432.750420.996432.680
1752/5/2021435.210443.200430.912439.890
1762/8/2021444.020449.195431.820434.630
1772/9/2021436.460484.850433.530467.300
1782/10/2021469.220478.650449.420469.690
1792/11/2021474.390479.950462.955465.690
1802/12/2021463.850470.350457.060468.670
1812/16/2021476.990486.720466.280469.700
1822/17/2021456.220457.250436.000456.970
1832/18/2021448.500462.500441.770452.990
1842/19/2021462.030472.826442.770467.310
1852/22/2021458.775483.700434.010437.280
1862/23/2021413.010424.400381.030415.870
1872/24/2021410.000419.999400.000413.790
1882/25/2021406.240418.942378.000382.340
1892/26/2021391.400401.879380.660395.480
1903/1/2021407.080420.500397.200420.310
1913/2/2021425.980429.440388.700389.670
1923/3/2021395.010402.980367.900369.500
1933/4/2021366.000375.561345.540359.950
1943/5/2021358.500361.910322.430353.540
1953/8/2021347.530356.990325.000327.190
1963/9/2021348.502362.860340.410361.110
1973/10/2021369.080376.280352.960356.540
1983/11/2021370.000370.000358.940363.340
1993/12/2021350.140361.480346.140359.960
2003/15/2021358.980363.450354.060362.920
2013/16/2021364.500369.600348.530353.580
2023/17/2021346.250370.990341.290368.710
2033/18/2021359.030361.350341.380341.950
2043/19/2021342.600350.550334.040347.510
2053/22/2021352.898361.330350.975356.320
2063/23/2021357.120363.870341.530342.240
2073/24/2021344.010345.100317.050318.450
2083/25/2021311.250322.680307.260317.620
2093/26/2021315.648318.784292.920306.820
2103/29/2021303.820308.750293.610300.790
2113/30/2021303.704314.810296.360311.960
2123/31/2021316.000329.000314.010325.770
2134/1/2021337.670343.420329.110331.900
2144/5/2021338.010338.010322.580328.290
2154/6/2021333.850363.080331.400359.370
2164/7/2021355.720367.780352.010361.920
2174/8/2021365.950378.000365.660372.680
2184/9/2021370.000375.976363.194373.660
2194/12/2021371.630374.650361.690371.120
2204/13/2021376.950389.607374.900387.970
2214/14/2021390.828397.690373.470374.660
2224/15/2021381.130392.750376.250388.640
2234/16/2021387.610388.390375.690376.520
2244/19/2021370.260378.745354.400361.150
2254/20/2021360.790366.560349.190354.850
2264/21/2021341.620356.968336.270356.620
2274/22/2021354.540367.380346.630350.060
2284/23/2021355.460357.600347.660356.870
2294/26/2021358.990368.980347.540368.630
2304/27/2021371.000376.500359.560360.650
2314/28/2021359.300364.840354.700357.740
2324/29/2021370.570377.500353.170356.700
2334/30/2021349.410352.650341.850342.970
2345/3/2021346.393346.393332.030335.310
2355/4/2021329.550330.390310.710319.590
2365/5/2021327.460328.000301.838304.170
2375/6/2021298.500300.270272.410284.180
2385/7/2021321.990337.240313.690317.000
2395/10/2021309.260316.240299.130301.410
2405/11/2021289.400316.630287.659314.120
2415/12/2021305.500322.637305.000308.030
2425/13/2021314.550323.500303.030309.600
2435/14/2021314.100317.842305.500315.950
2445/17/2021313.436322.600305.650312.430
2455/18/2021314.210332.410310.460325.530
2465/19/2021318.770323.700313.040320.840
2475/20/2021326.737336.920323.700335.980
2485/21/2021338.000339.890330.410330.650
2495/24/2021333.455351.430332.560347.220
2505/25/2021349.410356.700344.143345.700
2515/26/2021348.880352.880345.500348.990
2525/27/2021348.300349.800342.740349.030
2535/28/2021351.000357.390346.000346.710
2546/1/2021348.970352.990340.500347.710
2556/2/2021347.050347.710336.160340.240
2566/3/2021333.010336.980323.500323.800
2576/4/2021328.550333.147325.550327.120
2586/7/2021327.120342.350325.295341.060
2596/8/2021345.000345.760332.832340.130
2606/9/2021341.910346.310338.640339.640
ROKU-3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D260Expression=IF(AND(D2<D1,D2<D3),AND(C2<C1,C2<C3))textNO
C2:C260Expression=IF(AND(C2>C1,C2>C3),AND(D2>D1,D2>D3))textNO
D2:D257Expression=AND(D2<D1,D2<D3)textNO
C2:C257Expression=AND(C2>C1,C2>C3)textNO
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Is this what you mean
Excel Formula: 
=AND(C2>C1,C2>C3,D2>D1,D2>D3)
 
Upvote 0
Fluff said:
Is this what you mean
Excel Formula: 
=AND(C2>C1,C2>C3,D2>D1,D2>D3)
Click to expand...
Yes, but I want to apply the formula {=AND(C2>C1,C2>C3,D2>D1,D2>D3)} to the result of this formula. Can the formula "loop" ?
 
Upvote 0
Sorry but I don't understand what you mean.
 
Upvote 0
Fluff said:
Sorry but I don't understand what you mean.
Click to expand...
I am highlighting the high where there is a lower high on each side. Likewise, I am highlighting the lows where there is a higher low on both side. This identifies short term highs and lows. Conditional formating performs this well, but I would like to take it to the next level. I would like to take the result of the formula { =AND(C2>C1,C2>C3,D2>D1,D2>D3) } and highlight the highs and lows again creating an intermediate term high or low. For example: Row C17 (134.81) is the result of the formula =AND(C2>C1,C2>C3,D2>D1,D2>D3)...... Rows C9 (116.28) and row C21 (126.55) are also results of the same formula. If you applied the formula again it would only highlight row C17 as both rows C9 and C21 are again lower highs that row C17.
 
Upvote 0
I'm afraid I have no idea how to do that.
Maybe someone else will step in.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
151
kerm007
K
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
349
Peter_SSs
Peter_SSs
omsumo
  • Solved
if a formula in excel with multible conditions
2
Replies
19
Views
369
arthurbr
arthurbr
S
  • Question
Check if a previously drawn number is in a group.
Replies
1
Views
260
Excel Novice 1
E
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
240
amolvijay
A

Forum statistics

Threads
1,214,594
Messages
6,120,436
Members
448,964
Latest member
Danni317

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top