Shade cells based on criteria

[dyana]

Hello,

My goal is to shade cells that represent certain months.

For example, within Excel if A2 through A17 shows
1,2,3,4,5,6,7,8,9,10,11,12, 5,6, 8,2, (numbers represent the month number i.e. 1 for Jan.)
and b1 through b12 shows
Jan, Feb, Mar,....Dec

I want to shade 5 columns starting with the month that corresponds to the months number shown in column A. For instance, in A1 the number 1 would shade from January through May (5 columns), and A2 with the number 2 would shade columns February through June, etc.

I tried to create a macro that would do this, yet it does not work for the numbers 7,8,9,10,11, and 12 representing July through Dec.

The below email address is where I placed a sample of the excel file with the current code

http://64.4.20.250:80/cgi-bin/linkr..._action=http://www.prayer-chain.org/Trial.xls

The excel file shown at the above address actually shades a a number of columns that based on a lead time. To make it easier, however above I just am assuming a consistent 5 columns.


I don't necessarily have to use the code that I created if there is an easier method.


I appreciate any assistance.
Thanks, Dyana

 

[DaleWatson123321]

Hi Dyana,

I've created an example model based on your description, utilizing Conditional Formatting.

I attempted to access the file through your link, but was unable to get through.

Steps I used:

1) B1:M1 - enter labels Jan - Dec

2) A2:A13 - enter numbers 1 - 12

3) B2 - format with Conditional Formatting, as follows...
a) Format - Conditional Formatting
b) "Formula Is"
c) <Tab> to formula box.
d) enter: =$A2=1
e) click &quot;Format&quot; - &quot;Patterns&quot; - and choose color

4) Copy B2 - B2:F2 (Jan - May)

5) Copy B2:F2 and Paste at C3

6) While destination (C3:G3) is still highlighted, choose Conditional Formatting: Format - Conditional Formatting (or shortcut method: <Alt> O - D

7) Hit <Tab>,

8) Hit <F2> (Edit key)

9) Hit <Left Arrow> key

10) Hit <Delete> key to eliminate the &quot;1&quot;

11) Enter the number &quot;2&quot;

This replaces the entire range of 5 columns.

Repeat the above steps for the additional rows, but copy from the LAST copied row. So, for the next step, copy the range already highlighted (C3:G3), and paste at D4.

I hope this helps. Please advise as to how you make out. If you run into any difficulty, I can email you the file I created.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dyana]

Thanks Dale,

There's another step in this process that I really did not mention and was wondering if the conditional formatting will help. Since I have a time-critical deadline any help is appreciate.

The situation of the starting month and month column to start shading will change based on the number shown in column A. Each month the number will change, for example &quot;7&quot; for July, then &quot;8&quot; for August. Therefore, I will need to create a VBA macro, whether a recorded Excel process or creative VBA code, to support the monthly process.

Additionally, the number of total columns to shade within each row will be based on a lead time factor that will vary for each row item that is downloaded. Utilizing the example above, the lead time would probably reside in column &quot;B&quot; and the months in columns &quot;C - N&quot;.

In the original question, I made it simply the same number of columns. If I use conditional formatting, how do I accomodate for the combination of the month number and the lead time? Example being if the month numbe is &quot;7&quot; and the leadtime is 65. I want the shading for the particular row the conditional formatting is applied to, to reside on the column corresponding with July (#7) through September (65 days/30 days = over 2 months). The next row may go from July through August, with a lead time of say 55.

Again, I'm in a bind any help in much appreciated.

Thanks,
Dyana

 

[DaleWatson123321]

Hi Dyana,

I've made the following modications. I believe these will provide the results you're seeking.

Steps:

1) Inserted two columns: Column B for the &quot;Lead&quot; days, and Column C for the following formula: =ROUND(B2/30,0) to provide the number of months. Column C can be hidden if preferred.

2) Conditional Formatting formulas...

Jan - Cell D2: =AND($A2<2,$A2+$C2>=1)
Feb - Cell E2: =AND($A2<3,$A2+$C2>=2)
Mar - Cell F2: =AND($A2<4,$A2+$C2>=3)
Apr - Cell G2: =AND($A2<5,$A2+$C2>=4)
May - Cell H2: =AND($A2<6,$A2+$C2>=5)
Jun - Cell I2: =AND($A2<7,$A2+$C2>=6)
Jul - Cell J2: =AND($A2<8,$A2+$C2>=7)
Aug - Cell K2: =AND($A2<9,$A2+$C2>=8)
Sep - Cell L2: =AND($A2<10,$A2+$C2>=9)
Oct - Cell M2: =AND($A2<11,$A2+$C2>=10)
Nov - Cell N2: =AND($A2<12,$A2+$C2>=11)
Dec - Cell O2: =AND($A2<13,$A2+$C2>=12)

Once you've entered the Conditional Formatting formulas for row 2, you can copy C2:02 down for all the rows you require.

I hope this is what you require. Please advise. If you'd like I can email the revised file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca