Month Conditional Formatting

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,568
Office Version
  1. 365
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.
I thought that would probably be the case. A dozen or so columns by 2000 rows is not very big in the scheme of (Excel) things.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,649
Members
417,159
Latest member
Mayozero

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