MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 348
- Office Version
- 2013
- Platform
- 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.
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 | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | $2,000 | $4,000 | $8,200 | $11,100 | $13,800 | $16,500 | $19,200 | $21,900 | $24,600 | $27,300 | $30,000 | $32,700 | Expected Coversion $ To Kč | ||||||||
| 2 | $ Amt | Time of Data | Date of Data | April | May | June | July | August | September | October | November | December | January | February | March | $100 | |||||
| 3 | August | ïïïï | Fortissimo Rate When $'s Exchanged For Kč | 24.1299000 | August | ||||||||||||||||
| 4 | April | $100 | 22:30:21 | Wed-01 April 2020 | 22.237 | 22.270 | 22.288 | 22.299 | 22.312 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 24.1299000 | 2,223.70 Kč | Wed-01 Apr 2020 | ||
| 5 | April | $100 | 15:26:19 | Thu-02 April 2020 | 22.237 | 22.270 | 22.288 | 22.299 | 22.312 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 24.0107000 | 2,223.70 Kč | Thu-02 Apr 2020 | ||
| 6 | May | $100 | 3:20:42 | Fri-01 May 2020 | 22.007 | 22.042 | 22.059 | 22.070 | 22.081 | 22.092 | 22.092 | 22.094 | 22.094 | 22.092 | 22.094 | 22.094 | 23.7236000 | 2,204.20 Kč | Fri-01 May 2020 | ||
| 7 | May | $100 | 22:23:46 | Sat-02 May 2020 | 22.175 | 22.210 | 22.228 | 22.239 | 22.252 | 22.263 | 22.263 | 22.265 | 22.265 | 22.263 | 22.265 | 22.265 | 23.6680000 | 2,221.00 Kč | Sat-02 May 2020 | ||
| 8 | June | $100 | 8:58:48 | Mon-01 June 2020 | 24.226 | 24.245 | 24.260 | 24.274 | 24.286 | 24.300 | 24.303 | 24.303 | 24.303 | 24.303 | 24.303 | 24.303 | 24.1299000 | 2,426.00 Kč | Mon-01 Jun 2020 | ||
| 9 | June | $100 | 3:58:14 | Tue-02 June 2020 | 24.226 | 24.245 | 24.260 | 24.274 | 24.286 | 24.300 | 24.303 | 24.303 | 24.303 | 24.303 | 24.303 | 24.303 | 24.0107000 | 2,426.00 Kč | Tue-02 Jun 2020 | ||
| 10 | July | $100 | 10:22:26 | Wed-01 July 2020 | 23.560 | 23.583 | 23.604 | 23.616 | 23.630 | 23.639 | 23.639 | 23.639 | 23.639 | 23.639 | 23.639 | 23.639 | 23.7236000 | 2,361.60 Kč | Wed-01 Jul 2020 | ||
| 11 | July | $100 | 22:30:21 | Thu-02 July 2020 | 23.459 | 23.484 | 23.505 | 23.515 | 23.524 | 23.533 | 23.540 | 23.540 | 23.540 | 23.540 | 23.540 | 23.540 | 23.6680000 | 2,351.50 Kč | Thu-02 Jul 2020 | ||
| 12 | August | $100 | 4:20:09 | Sat-01 August 2020 | 22.165 | 22.200 | 22.218 | 22.229 | 22.242 | 22.253 | 22.253 | 22.253 | 22.253 | 22.253 | 22.253 | 22.253 | 22.2966000 | 2,224.20 Kč | Sat-01 Aug 2020 | ||
| 13 | August | $100 | 3:20:42 | Sun-02 August 2020 | 22.165 | 22.200 | 22.218 | 22.229 | 22.242 | 22.253 | 22.253 | 22.253 | 22.253 | 22.253 | 22.253 | 22.253 | 22.2966000 | 2,224.20 Kč | Sun-02 Aug 2020 | ||
| 14 | September | $100 | 7:23:06 | Tue-01 September 2020 | 22.083 | 22.118 | 22.136 | 22.147 | 22.158 | 22.171 | 22.171 | 22.171 | 22.171 | 22.171 | 22.171 | 22.171 | 22.3210000 | 2,217.10 Kč | Tue-01 Sep 2020 | ||
| 15 | September | $100 | 13:21:22 | Wed-02 September 2020 | 22.237 | 22.270 | 22.288 | 22.299 | 22.312 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.2649000 | 2,232.30 Kč | Wed-02 Sep 2020 | ||
| 16 | October | $100 | 21:02:46 | Thu-01 October 2020 | 22.237 | 22.270 | 22.288 | 22.299 | 22.312 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.1820000 | 2,232.30 Kč | Thu-01 Oct 2020 | ||
| 17 | October | $100 | 7:27:27 | Fri-02 October 2020 | 22.007 | 22.042 | 22.059 | 22.070 | 22.081 | 22.092 | 22.092 | 22.094 | 22.094 | 22.092 | 22.094 | 22.094 | 22.1033000 | 2,209.20 Kč | Fri-02 Oct 2020 | ||
| 18 | November | $100 | 2:28:45 | Sun-01 November 2020 | 22.076 | 22.111 | 22.129 | 22.140 | 22.151 | 22.164 | 22.164 | 22.164 | 22.164 | 22.164 | 22.164 | 22.164 | 22.1062000 | 2,216.40 Kč | Sun-01 Nov 2020 | ||
| 19 | November | $100 | 2:28:45 | Mon-02 November 2020 | 21.627 | 21.666 | 21.681 | 21.694 | 21.704 | 21.715 | 21.719 | 21.719 | 21.719 | 21.719 | 21.719 | 21.719 | 21.9543000 | 2,171.90 Kč | Mon-02 Nov 2020 | ||
| 20 | December | $100 | 7:50:05 | Tue-01 December 2020 | 22.136 | 22.171 | 22.189 | 22.200 | 22.211 | 22.224 | 22.226 | 22.226 | 22.226 | 22.226 | 22.226 | 22.226 | 22.2696000 | 2,222.60 Kč | Tue-01 Dec 2020 | ||
| 21 | December | $100 | 4:20:09 | Wed-02 December 2020 | 22.237 | 22.270 | 22.288 | 22.299 | 22.312 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 24.1299000 | 2,232.30 Kč | Wed-02 Dec 2020 | ||
| 22 | January | $100 | 22:23:46 | Wed-01 January 2020 | 22.237 | 22.270 | 22.288 | 22.299 | 22.312 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 23.7053000 | 2,232.30 Kč | Wed-01 Jan 2020 | ||
| 23 | January | $100 | 7:23:06 | Thu-02 January 2020 | 22.007 | 22.042 | 22.059 | 22.070 | 22.081 | 22.092 | 22.092 | 22.094 | 22.094 | 22.092 | 22.094 | 22.094 | 23.7236000 | 2,209.20 Kč | Thu-02 Jan 2020 | ||
| 24 | February | $100 | 18:26:04 | Sat-01 February 2020 | 22.076 | 22.111 | 22.129 | 22.140 | 22.151 | 22.164 | 22.164 | 22.164 | 22.164 | 22.164 | 22.164 | 22.164 | 23.7449000 | 2,216.40 Kč | Sat-01 Feb 2020 | ||
| 25 | February | $100 | 21:02:46 | Sun-02 February 2020 | 21.627 | 21.666 | 21.681 | 21.694 | 21.704 | 21.715 | 21.719 | 21.719 | 21.719 | 21.719 | 21.719 | 21.719 | 24.1299000 | 2,171.90 Kč | Sun-02 Feb 2020 | ||
| 26 | March | $100 | 10:22:26 | Sun-01 March 2020 | 22.136 | 22.171 | 22.189 | 22.200 | 22.211 | 22.224 | 22.226 | 22.226 | 22.226 | 22.226 | 22.226 | 22.226 | 23.7053000 | 2,222.60 Kč | Sun-01 Mar 2020 | ||
| 27 | March | $100 | 22:30:21 | Mon-02 March 2020 | 22.237 | 22.270 | 22.288 | 22.299 | 22.312 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 22.323 | 23.7236000 | 2,232.30 Kč | Mon-02 Mar 2020 | ||
2RowCondFormats | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E1 | E1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$5 |
| F1 | F1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$6 |
| G1 | G1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$7 |
| H1 | H1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$8 |
| I1 | I1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$9 |
| J1 | J1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$10 |
| K1 | K1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$11 |
| L1 | L1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$12 |
| M1 | M1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$13 |
| N1 | N1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$14 |
| O1 | O1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$15 |
| P1 | P1 | ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]Current Rates'!$M$16 |
| C3 | C3 | =TEXT(TODAY(),"MMMM") |
| Q3 | Q3 | =MAX(Q5:Q1079) |
| S3 | S3 | =C3 |
| A4:A27 | A4 | =IF(D4<>"",TEXT(D4,"MMMM"),"") |
| B4:B27 | B4 | =$S$2 |
| R4:R5 | R4 | =SUM(E4*$S$2) |
| S4:S27 | S4 | =D4 |
| R6:R7 | R6 | =SUM(F6*$S$2) |
| R8:R9 | R8 | =SUM(G8*$S$2) |
| R10:R11 | R10 | =SUM(H10*$S$2) |
| R12:R13 | R12 | =SUM(I12*$S$2) |
| R14:R15 | R14 | =SUM(J14*$S$2) |
| R16:R17 | R16 | =SUM(K16*$S$2) |
| R18:R19 | R18 | =SUM(L18*$S$2) |
| R20:R21 | R20 | =SUM(M20*$S$2) |
| R22:R23 | R22 | =SUM(N22*$S$2) |
| R24:R25 | R24 | =SUM(O24*$S$2) |
| R26:R27 | R26 | =SUM(P26*$S$2) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| '2RowCondFormats'!MONTH_NAMES | ='2RowCondFormats'!$D$5:$D$3172 | S5, A5 |
