Month Conditional Formatting

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
221
Office Version
  1. 2013
Platform
  1. Windows
If a range in a column contains a specific month using the format Mon-01 June 2020 then I want to set conditional formatting on that range.
Different conditional formatting would apply for each of the other 12 months, plus the one column “R” labeled “Expected Conversion $ To Kč” would have the same conditional formatting per each month appear down through column “R” so as you look down through column R you would see different fill and text colors associated with the various month column fill and text color assignments as seen in the column headers showing in rows 1 & 2.
Thus when all conditional formatting is done then this sheet would appear like the attached XL2BB showing the formatting per the month shown in column D.
The conditional formatting would apply per the month shown in cells E2:P2 to all cells in each column based on the month showing in column A.
(A:1234 could just as easily be A:1111 or A:2222 or any number as long as the conditional formatting is far-reaching to include new data that is entered on a daily basis)
Data is entered more than once per day, sometimes 4 or 5 times a day depending on how many times per day I check the exchange rates.
If range A4:A1234 contains or equals a specific month, then conditional formatting is applied to the vertical range of cells in the column that matches the month name. If range A4:A1234 contains any blank cells (but obviously contains the formula: =IF(D9<>“”,TEXT(D9,“MMMM”),“”) as an example then conditional formatting should not apply to any of columns E thru P per rows that seem blank yet contain the formula.
Conditional formatting is based upon the month name that appears in column A and applying each range to a single formula to use in conditional formatting for each month column.
Many of the numbers showing in the XL2BB generated output are fake, but shown to acquire calculation results. This image shows only 2 days per month, however the sheet I use to keep track of Fortissimo Exchange history may show multiple rows of the same date, different time, as I check often during any given day.
So now the task involves acquiring a vertical range in each of the month columns based upon the month name that appears in column A and applying each range to a single formula to use in conditional formatting for each month column.
I want to use this conditional formatting so at a glance of any given row of specific month I can see which month column is used for calculation in column R (Expected Conversion $ To Kč).
So because there may be several rows of any given day’s entered rates then the formulas involved in acquisition of the vertical range in the column affected by the month name are dynamic and change as new data is entered in subsequent rows.
Cells in Row 2 showing the month names are exactly month names formatted to show as they do (example for April: 04/04/2020)
I hope this is not too much information. I suppose it seems I have repeated much in my explanation but in doing so I hope it causes no questions. If question do arise don’t hesitate to ask and I will provide answers.
CurrencyConversionRatesWebQuery.xlsm
ABCDEFGHIJKLMNOPQRS
1$2,000$4,000$8,200$11,100$13,800$16,500$19,200$21,900$24,600$27,300$30,000$32,700Expected Coversion $ To Kč
2$ AmtTime of DataDate of DataAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarch$100
3AugustïïïïFortissimo Rate When $'s Exchanged For Kč24.1299000August
4April$10022:30:21Wed-01 April 202022.23722.27022.28822.29922.31222.32322.32322.32322.32322.32322.32322.32324.12990002,223.70 KčWed-01 Apr 2020
5April$10015:26:19Thu-02 April 202022.23722.27022.28822.29922.31222.32322.32322.32322.32322.32322.32322.32324.01070002,223.70 KčThu-02 Apr 2020
6May$1003:20:42Fri-01 May 202022.00722.04222.05922.07022.08122.09222.09222.09422.09422.09222.09422.09423.72360002,204.20 KčFri-01 May 2020
7May$10022:23:46Sat-02 May 202022.17522.21022.22822.23922.25222.26322.26322.26522.26522.26322.26522.26523.66800002,221.00 KčSat-02 May 2020
8June$1008:58:48Mon-01 June 202024.22624.24524.26024.27424.28624.30024.30324.30324.30324.30324.30324.30324.12990002,426.00 KčMon-01 Jun 2020
9June$1003:58:14Tue-02 June 202024.22624.24524.26024.27424.28624.30024.30324.30324.30324.30324.30324.30324.01070002,426.00 KčTue-02 Jun 2020
10July$10010:22:26Wed-01 July 202023.56023.58323.60423.61623.63023.63923.63923.63923.63923.63923.63923.63923.72360002,361.60 KčWed-01 Jul 2020
11July$10022:30:21Thu-02 July 202023.45923.48423.50523.51523.52423.53323.54023.54023.54023.54023.54023.54023.66800002,351.50 KčThu-02 Jul 2020
12August$1004:20:09Sat-01 August 202022.16522.20022.21822.22922.24222.25322.25322.25322.25322.25322.25322.25322.29660002,224.20 KčSat-01 Aug 2020
13August$1003:20:42Sun-02 August 202022.16522.20022.21822.22922.24222.25322.25322.25322.25322.25322.25322.25322.29660002,224.20 KčSun-02 Aug 2020
14September$1007:23:06Tue-01 September 202022.08322.11822.13622.14722.15822.17122.17122.17122.17122.17122.17122.17122.32100002,217.10 KčTue-01 Sep 2020
15September$10013:21:22Wed-02 September 202022.23722.27022.28822.29922.31222.32322.32322.32322.32322.32322.32322.32322.26490002,232.30 KčWed-02 Sep 2020
16October$10021:02:46Thu-01 October 202022.23722.27022.28822.29922.31222.32322.32322.32322.32322.32322.32322.32322.18200002,232.30 KčThu-01 Oct 2020
17October$1007:27:27Fri-02 October 202022.00722.04222.05922.07022.08122.09222.09222.09422.09422.09222.09422.09422.10330002,209.20 KčFri-02 Oct 2020
18November$1002:28:45Sun-01 November 202022.07622.11122.12922.14022.15122.16422.16422.16422.16422.16422.16422.16422.10620002,216.40 KčSun-01 Nov 2020
19November$1002:28:45Mon-02 November 202021.62721.66621.68121.69421.70421.71521.71921.71921.71921.71921.71921.71921.95430002,171.90 KčMon-02 Nov 2020
20December$1007:50:05Tue-01 December 202022.13622.17122.18922.20022.21122.22422.22622.22622.22622.22622.22622.22622.26960002,222.60 KčTue-01 Dec 2020
21December$1004:20:09Wed-02 December 202022.23722.27022.28822.29922.31222.32322.32322.32322.32322.32322.32322.32324.12990002,232.30 KčWed-02 Dec 2020
22January$10022:23:46Wed-01 January 202022.23722.27022.28822.29922.31222.32322.32322.32322.32322.32322.32322.32323.70530002,232.30 KčWed-01 Jan 2020
23January$1007:23:06Thu-02 January 202022.00722.04222.05922.07022.08122.09222.09222.09422.09422.09222.09422.09423.72360002,209.20 KčThu-02 Jan 2020
24February$10018:26:04Sat-01 February 202022.07622.11122.12922.14022.15122.16422.16422.16422.16422.16422.16422.16423.74490002,216.40 KčSat-01 Feb 2020
25February$10021:02:46Sun-02 February 202021.62721.66621.68121.69421.70421.71521.71921.71921.71921.71921.71921.71924.12990002,171.90 KčSun-02 Feb 2020
26March$10010:22:26Sun-01 March 202022.13622.17122.18922.20022.21122.22422.22622.22622.22622.22622.22622.22623.70530002,222.60 KčSun-01 Mar 2020
27March$10022:30:21Mon-02 March 202022.23722.27022.28822.29922.31222.32322.32322.32322.32322.32322.32322.32323.72360002,232.30 KčMon-02 Mar 2020
2RowCondFormats
Cell Formulas
RangeFormula
E1E1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$5
F1F1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$6
G1G1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$7
H1H1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$8
I1I1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$9
J1J1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$10
K1K1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$11
L1L1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$12
M1M1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$13
N1N1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$14
O1O1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$15
P1P1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$16
C3C3=TEXT(TODAY(),"MMMM")
Q3Q3=MAX(Q5:Q1079)
S3S3=C3
A4:A27A4=IF(D4<>"",TEXT(D4,"MMMM"),"")
B4:B27B4=$S$2
R4:R5R4=SUM(E4*$S$2)
S4:S27S4=D4
R6:R7R6=SUM(F6*$S$2)
R8:R9R8=SUM(G8*$S$2)
R10:R11R10=SUM(H10*$S$2)
R12:R13R12=SUM(I12*$S$2)
R14:R15R14=SUM(J14*$S$2)
R16:R17R16=SUM(K16*$S$2)
R18:R19R18=SUM(L18*$S$2)
R20:R21R20=SUM(M20*$S$2)
R22:R23R22=SUM(N22*$S$2)
R24:R25R24=SUM(O24*$S$2)
R26:R27R26=SUM(P26*$S$2)
Named Ranges
NameRefers ToCells
'2RowCondFormats'!MONTH_NAMES='2RowCondFormats'!$D$5:$D$3172S5, A5
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,757
Office Version
  1. 365
Platform
  1. Windows
You need a different CF rule for each month column and 12 separate CF rules for column R.
I have used different colours (cell background only) and only done 2 of the columns and 2 of the 12 rules for column R.
You just need to choose your own formatting for each rule.

MrDB4Excel 2020-08-09 1.xlsm
ABCDEFGHIJKLMNOPQR
2$ AmtTime of DataDate of DataAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarch
3Augustïïïï24.1299
4April1000.93773912Wed-01 April 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32324.12992223.7
5April1000.643271528Thu-02 April 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32324.01072223.7
6May1000.139375Fri-01 May 202022.00722.04222.05922.0722.08122.09222.09222.09422.09422.09222.09422.09423.72362204.2
7May1000.933173495Sat-02 May 202022.17522.2122.22822.23922.25222.26322.26322.26522.26522.26322.26522.26523.6682221
8June1000.37417037Mon-01 June 202024.22624.24524.2624.27424.28624.324.30324.30324.30324.30324.30324.30324.12992426
9June1000.165441088Tue-02 June 202024.22624.24524.2624.27424.28624.324.30324.30324.30324.30324.30324.30324.01072426
10July1000.432243056Wed-01 July 202023.5623.58323.60423.61623.6323.63923.63923.63923.63923.63923.63923.63923.72362361.6
11July1000.93773912Thu-02 July 202023.45923.48423.50523.51523.52423.53323.5423.5423.5423.5423.5423.5423.6682351.5
12August1000.180659491Sat-01 August 202022.16522.222.21822.22922.24222.25322.25322.25322.25322.25322.25322.25322.29662224.2
13August1000.139375Sun-02 August 202022.16522.222.21822.22922.24222.25322.25322.25322.25322.25322.25322.25322.29662224.2
14September1000.307710069Tue-01 September 202022.08322.11822.13622.14722.15822.17122.17122.17122.17122.17122.17122.17122.3212217.1
15September1000.556508449Wed-02 September 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32322.26492232.3
16October1000.876915972Thu-01 October 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32322.1822232.3
17October1000.310723495Fri-02 October 202022.00722.04222.05922.0722.08122.09222.09222.09422.09422.09222.09422.09422.10332209.2
18November10022.1033Sun-01 November 202022.07622.11122.12922.1422.15122.16422.16422.16422.16422.16422.16422.16422.10622216.4
19November10022.1033Mon-02 November 202021.62721.66621.68121.69421.70421.71521.71921.71921.71921.71921.71921.71921.95432171.9
20December1000.326448495Tue-01 December 202022.13622.17122.18922.222.21122.22422.22622.22622.22622.22622.22622.22622.26962222.6
21December1000.180659491Wed-02 December 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32324.12992232.3
22January1000.933173495Wed-01 January 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32323.70532232.3
23January1000.307710069Thu-02 January 202022.00722.04222.05922.0722.08122.09222.09222.09422.09422.09222.09422.09423.72362209.2
24February1000.768096296Sat-01 February 202022.07622.11122.12922.1422.15122.16422.16422.16422.16422.16422.16422.16423.74492216.4
25February1000.876915972Sun-02 February 202021.62721.66621.68121.69421.70421.71521.71921.71921.71921.71921.71921.71924.12992171.9
26March1000.432243056Sun-01 March 202022.13622.17122.18922.222.21122.22422.22622.22622.22622.22622.22622.22623.70532222.6
27March1000.93773912Mon-02 March 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32323.72362232.3
28
CF Months
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R4:R100Expression=A4="May"textNO
R4:R100Expression=A4="April"textNO
F4:F100Expression=MONTH(F$2)=MONTH($D4)textNO
E4:E100Expression=MONTH(E$2)=MONTH($D4)textNO


As you are applying the CF rules (or afterwards in CF -> Manage rules) you also need to specify how far down the sheet you want this to extend. Note that CF is 'Volatile' so the more you have the more chance that it will begin to have an impact on the performance of your sheet.

1596968614420.png
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
221
Office Version
  1. 2013
Platform
  1. Windows
You need a different CF rule for each month column and 12 separate CF rules for column R.
I have used different colours (cell background only) and only done 2 of the columns and 2 of the 12 rules for column R.
You just need to choose your own formatting for each rule.

MrDB4Excel 2020-08-09 1.xlsm
ABCDEFGHIJKLMNOPQR
2$ AmtTime of DataDate of DataAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarch
3Augustïïïï24.1299
4April1000.93773912Wed-01 April 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32324.12992223.7
5April1000.643271528Thu-02 April 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32324.01072223.7
6May1000.139375Fri-01 May 202022.00722.04222.05922.0722.08122.09222.09222.09422.09422.09222.09422.09423.72362204.2
7May1000.933173495Sat-02 May 202022.17522.2122.22822.23922.25222.26322.26322.26522.26522.26322.26522.26523.6682221
8June1000.37417037Mon-01 June 202024.22624.24524.2624.27424.28624.324.30324.30324.30324.30324.30324.30324.12992426
9June1000.165441088Tue-02 June 202024.22624.24524.2624.27424.28624.324.30324.30324.30324.30324.30324.30324.01072426
10July1000.432243056Wed-01 July 202023.5623.58323.60423.61623.6323.63923.63923.63923.63923.63923.63923.63923.72362361.6
11July1000.93773912Thu-02 July 202023.45923.48423.50523.51523.52423.53323.5423.5423.5423.5423.5423.5423.6682351.5
12August1000.180659491Sat-01 August 202022.16522.222.21822.22922.24222.25322.25322.25322.25322.25322.25322.25322.29662224.2
13August1000.139375Sun-02 August 202022.16522.222.21822.22922.24222.25322.25322.25322.25322.25322.25322.25322.29662224.2
14September1000.307710069Tue-01 September 202022.08322.11822.13622.14722.15822.17122.17122.17122.17122.17122.17122.17122.3212217.1
15September1000.556508449Wed-02 September 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32322.26492232.3
16October1000.876915972Thu-01 October 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32322.1822232.3
17October1000.310723495Fri-02 October 202022.00722.04222.05922.0722.08122.09222.09222.09422.09422.09222.09422.09422.10332209.2
18November10022.1033Sun-01 November 202022.07622.11122.12922.1422.15122.16422.16422.16422.16422.16422.16422.16422.10622216.4
19November10022.1033Mon-02 November 202021.62721.66621.68121.69421.70421.71521.71921.71921.71921.71921.71921.71921.95432171.9
20December1000.326448495Tue-01 December 202022.13622.17122.18922.222.21122.22422.22622.22622.22622.22622.22622.22622.26962222.6
21December1000.180659491Wed-02 December 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32324.12992232.3
22January1000.933173495Wed-01 January 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32323.70532232.3
23January1000.307710069Thu-02 January 202022.00722.04222.05922.0722.08122.09222.09222.09422.09422.09222.09422.09423.72362209.2
24February1000.768096296Sat-01 February 202022.07622.11122.12922.1422.15122.16422.16422.16422.16422.16422.16422.16423.74492216.4
25February1000.876915972Sun-02 February 202021.62721.66621.68121.69421.70421.71521.71921.71921.71921.71921.71921.71924.12992171.9
26March1000.432243056Sun-01 March 202022.13622.17122.18922.222.21122.22422.22622.22622.22622.22622.22622.22623.70532222.6
27March1000.93773912Mon-02 March 202022.23722.2722.28822.29922.31222.32322.32322.32322.32322.32322.32322.32323.72362232.3
28
CF Months
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R4:R100Expression=A4="May"textNO
R4:R100Expression=A4="April"textNO
F4:F100Expression=MONTH(F$2)=MONTH($D4)textNO
E4:E100Expression=MONTH(E$2)=MONTH($D4)textNO


As you are applying the CF rules (or afterwards in CF -> Manage rules) you also need to specify how far down the sheet you want this to extend. Note that CF is 'Volatile' so the more you have the more chance that it will begin to have an impact on the performance of your sheet.

View attachment 19978
Thanks, Peter, I will give this a shot and let you know how it goes.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
221
Office Version
  1. 2013
Platform
  1. Windows
Excellent, Peter, works very well. The CF rules I set to for example for June: the first rule applies to G4 down to G120 while 2nd rule for R column applies to R4 down to R120. Consequently for July: the first rule applies to H4 down to H240 while 2nd rule for R column applies to R4 down to R240. I do this in case there is overlap plus it keeps it simple to remember the range. Plus this worksheet will only be for the second half of 2020 (as I did not begin keeping track of Fortissimo exchange rates until June 1) January 2021 I will begin a new worksheet for the first half of 2021 and so on and so on. This should keep the sheet manageable in that it won't get to overloaded. When the second half of 2020 is done then I move that sheet out to a separate file copying all only as text without any formulas. I use a free program called Pure Text that allows me to paste anything less all formatting. In other words, say I copy clip something from a webpage and it contains images. The images will not paste, only the text that came from the website, and without the HTML formatting that the text contained while on the website. Very handy utility. I have been using it for a very long time for many applications.
1596984012932.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,757
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you have it working. (y)

My only comment relates to this:
the first rule applies to G4 down to G120 while 2nd rule for R column applies to R4 down to R120. Consequently for July: the first rule applies to H4 down to H240 while 2nd rule for R column applies to R4 down to R240. I do this in case there is overlap plus it keeps it simple to remember the range.
Just suppose that the last rule you applied went to row 2000. Wouldn't it be safer regarding overlaps and even simpler to remember ranges if all rules applied to rows 4:2000 instead of having different row ranges for different rules?
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
221
Office Version
  1. 2013
Platform
  1. Windows
Good point. Can I assume that having all rules run on the range 4:2000 is not overkill? Definitely would be easier to remember.
Again, thanks, Peter for your valuable input.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,757
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can I assume that having all rules run on the range 4:2000 is not overkill?
It does depend on your machine, other worksheet formulas etc but I think it should be fine. Only way to tell is to give it a go. :)


Again, thanks, Peter for your valuable input.
You're welcome. :)
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
221
Office Version
  1. 2013
Platform
  1. Windows
My system is pretty good I think, although I would like more RAM
Operating System
Windows 10 Pro 64-bit
CPU
Intel Xeon E3 1220 v3 @ 3.10GHz 42 °C
Haswell-WS 22nm Technology
RAM
8.00GB Dual-Channel DDR3 @ 798MHz (11-11-11-28)
Motherboard
Dell Inc. 073MMW (SOCKET 0)
Graphics
S24B420 (1920x1200@59Hz)
S24B420 (1920x1200@59Hz)
256MB NVIDIA Quadro NVS 295 (NVIDIA) 73 °C
Storage
931GB Seagate ST1000DM003-1CH162 (SATA ) 37 °C
238GB SAMSUNG SSD SM841 2.5" 7mm 256GB (SATA (SSD))
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
221
Office Version
  1. 2013
Platform
  1. Windows
So far it works very well, as if there is no conditional formatting present, just as fast with using the file as before w/o CF. No noticeable difference.
Again Peter, many thanks for the simple solution you provided.
 

Forum statistics

Threads
1,147,815
Messages
5,743,366
Members
423,790
Latest member
kevinlee_5

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
Top