Colour cells Calendar

[rpfc37]

Hi,

Been searching everywhere and havent been able to find it - apologies if double.

Sheet 1: Ive got 2 columns, 1 for the dates of the year (column A) and column B for respective budgeted attendances (1,000, 2000, all the way to 20,000).
Ive also created in coluns D and E: white (closed - zero attendance), (yellow - 1 - 2,000 attendance), (blue - 2,001 to 5,000 attendance), green (5,001 to
10,000 attendance), orange (10,001 to 15,000 attendance) and red (15,000 - 20,000 attendance).

Sheet 2: I created a manual calendar in excel for each of the days of the year, showing 1, 2, 3, to 31 (or however many days a month has) and have done this for each of the 12 months.

M

 

[rpfc37]

Sorry, something went wrong.

To continue. I need to colour each of the days of the year in sheet 2 to match the respective colours shown in sheet 1, depending on the budgeted attendance of each of the days.

Ive been trying to work with index, match as well as lookup and conditional formatting, but no luck so far. I dont want to create 1 formual for everyday of the year, but just 6 formulas as i have 6 colours.

Any ideas?

Thanks,
A.

 

[Perpa]

A,
Welcome to the Forum.
You did not provide a screenshot of either sheet so I made a few assumptions:
Sheet 1 is named 'Sheet1', and Sheet 2 is 'Sheet2'.
I assumed you had a header row on Sheet1 that looks similar to the following for January, February, and March:

Sheet1

	A	B	C	D	E
1	Date	Budget_Att		Color	Actual_Att
2	1/1/2018	20000
3	1/2/2018
4	1/3/2018
5	1/4/2018	10000
6	1/5/2018	4600
7	1/6/2018	4600
8	1/7/2018	4600
9	1/8/2018	4600
10	1/9/2018	4600
11	1/10/2018	9200
12	1/11/2018	9200
13	1/12/2018	9200
14	1/13/2018	9200
15	1/14/2018	9200
16	1/15/2018	9200
17	1/16/2018	9200
18	1/17/2018	2500
19	1/18/2018	2500
20	1/19/2018	2500
21	1/20/2018	2500
22	1/21/2018	12000
23	1/22/2018	12000
24	1/23/2018	12000
25	1/24/2018	12000
26	1/25/2018	12000
27	1/26/2018	12000
28	1/27/2018	12000
29	1/28/2018	12000
30	1/29/2018	12000
31	1/30/2018	5400
32	1/31/2018	2000
33
34	2/1/2018	4600
35	2/2/2018	4600

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A3 =A2+1 A4 =A3+1 A5 =A4+1 A6 =A5+1 A7 =A6+1 A8 =A7+1 A9 =A8+1 A10 =A9+1 A11 =A10+1 A12 =A11+1 A13 =A12+1 A14 =A13+1 A15 =A14+1 A16 =A15+1 A17 =A16+1 A18 =A17+1 A19 =A18+1 A20 =A19+1 A21 =A20+1 A22 =A21+1 A23 =A22+1 A24 =A23+1 A25 =A24+1 A26 =A25+1 A27 =A26+1 A28 =A27+1 A29 =A28+1 A30 =A29+1 A31 =A30+1 A32 =A31+1 A34 =A32+1 A35 =A34+1 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Sheet 2 could be in many formats, I assumed the following for January, February, and March:

Sheet2

	A	B	C	D	E	F	G
1	January	2018
2	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3		1	2	3	4	5	6
4	7	8	9	10	11	12	13
5	14	15	16	17	18	19	20
6	21	22	23	24	25	26	27
7	28	29	30	31
8
9	February
10	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
11					1	2	3
12	4	5	6	7	8	9	10
13	11	12	13	14	15	16	17
14	18	19	20	21	22	23	24
15	25	26	27	28
16
17	March
18	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
19					1	2	3
20	4	5	6	7	8	9	10
21	11	12	13	14	15	16	17
22	18	19	20	21	22	23	24
23	25	26	27	28	29	30	31

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C3 =B3+1 D3 =C3+1 E3 =D3+1 F3 =E3+1 G3 =F3+1 A4 =G3+1 B4 =A4+1 C4 =B4+1 D4 =C4+1 E4 =D4+1 F4 =E4+1 G4 =F4+1 A5 =G4+1 B5 =A5+1 C5 =B5+1 D5 =C5+1 E5 =D5+1 F5 =E5+1 G5 =F5+1 A6 =G5+1 B6 =A6+1 C6 =B6+1 D6 =C6+1 E6 =D6+1 F6 =E6+1 G6 =F6+1 A7 =G6+1 B7 =A7+1 C7 =B7+1 D7 =C7+1 F11 =E11+1 G11 =F11+1 A12 =G11+1 B12 =A12+1 C12 =B12+1 D12 =C12+1 E12 =D12+1 F12 =E12+1 G12 =F12+1 A13 =G12+1 B13 =A13+1 C13 =B13+1 D13 =C13+1 E13 =D13+1 F13 =E13+1 G13 =F13+1 A14 =G13+1 B14 =A14+1 C14 =B14+1 D14 =C14+1 E14 =D14+1 F14 =E14+1 G14 =F14+1 A15 =G14+1 B15 =A15+1 C15 =B15+1 D15 =C15+1 F19 =E19+1 G19 =F19+1 A20 =G19+1 B20 =A20+1 C20 =B20+1 D20 =C20+1 E20 =D20+1 F20 =E20+1 G20 =F20+1 A21 =G20+1 B21 =A21+1 C21 =B21+1 D21 =C21+1 E21 =D21+1 F21 =E21+1 G21 =F21+1 A22 =G21+1 B22 =A22+1 C22 =B22+1 D22 =C22+1 E22 =D22+1 F22 =E22+1 G22 =F22+1 A23 =G22+1 B23 =A23+1 C23 =B23+1 D23 =C23+1 E23 =D23+1 F23 =E23+1 G23 =F23+1 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Here is the code I used to identify the calendar dates on Sheet 2 and add the corresponding color for the 6 'Budget Attendance' ranges. You may need to adjust the ranges depending on how you set up your calendar on Sheet 2. (See red font and make changes and add cases for months April to December).

Copy and paste this code into a standard code module...Cntl+F11, paste into the window that opens, close the window, Save the file as macro enabled. You may have to enable macros when you open the file depending on the version of Excel you are using.
Perpa

Sub Test()
Dim rw1, LastRow1 As Long
Dim c As Range
Dim cur_Month, cur_Day As Long
    
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
    For rw1 = 2 To LastRow1
    
        If Cells(rw1, "B") <> 0 Then      'Column E has the ATTENDANCE
            att = Cells(rw1, "B")
            cur_Month = Month(Cells(rw1, "A"))
            cur_Day = Day(Cells(rw1, "A"))
    
            Select Case cur_Month
                Case 1             'January
                    With Sheets("Sheet2").Range("[COLOR=#FF0000]A3:G8[/COLOR]")
                        Set c = .Find(What:=cur_Day, LookIn:=xlValues, LookAt:=xlWhole)
                    End With
                Case 2              'February
                    With Sheets("Sheet2").Range[COLOR=("[COLOR=#FF0000]A11:G16[/COLOR]")
                        Set c = .Find(What:=cur_Day, LookIn:=xlValues, LookAt:=xlWhole)
                    End With
                Case 3              'March
                    With Sheets("Sheet2").Range[COLOR=("[COLOR=#FF0000]A19:G24[/COLOR]")
                        Set c = .Find(What:=cur_Day, LookIn:=xlValues, LookAt:=xlWhole)
                    End With
 You would continue  with Case 4 to Case 12 as above for April to December
            End Select
            
            If att >= 1 And att <= 2000 Then c.Interior.Color = vbYellow
            If att >= 2001 And att <= 5000 Then c.Interior.Color = vbBlue
            If att >= 5001 And att <= 10000 Then c.Interior.Color = vbGreen
            If att >= 10001 And att <= 15001 Then c.Interior.ColorIndex = 46    'Orange Fill
            If att >= 15001 Then c.Interior.Color = vbRed
        End If
   Next rw1
Application.ScreenUpdating = True
Sheets("Sheet2").Select
End Sub

 

[Perpa]

A,
Here is a copy of Sheet 2 after the macro has run...

https://imgur.com/oJb95Bp

Perpa