Colour cells Calendar

R

rpfc37

New Member
Joined
Oct 31, 2017
Messages
2
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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.
 
Upvote 0
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
Date
Budget_Att
Color
Actual_Att

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]1/1/2018
[/TD]
[TD="align: right"]20000
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]1/2/2018
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]1/3/2018
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]1/4/2018
[/TD]
[TD="align: right"]10000
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]1/5/2018
[/TD]
[TD="align: right"]4600
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]1/6/2018
[/TD]
[TD="align: right"]4600
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]1/7/2018
[/TD]
[TD="align: right"]4600
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]1/8/2018
[/TD]
[TD="align: right"]4600
[/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]1/9/2018
[/TD]
[TD="align: right"]4600
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"]1/10/2018
[/TD]
[TD="align: right"]9200
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"]1/11/2018
[/TD]
[TD="align: right"]9200
[/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"]1/12/2018
[/TD]
[TD="align: right"]9200
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"]1/13/2018
[/TD]
[TD="align: right"]9200
[/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"]1/14/2018
[/TD]
[TD="align: right"]9200
[/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"]1/15/2018
[/TD]
[TD="align: right"]9200
[/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"]1/16/2018
[/TD]
[TD="align: right"]9200
[/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"]1/17/2018
[/TD]
[TD="align: right"]2500
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"]1/18/2018
[/TD]
[TD="align: right"]2500
[/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"]1/19/2018
[/TD]
[TD="align: right"]2500
[/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"]1/20/2018
[/TD]
[TD="align: right"]2500
[/TD]

[TD="align: center"]22
[/TD]
[TD="align: right"]1/21/2018
[/TD]
[TD="align: right"]12000
[/TD]

[TD="align: center"]23
[/TD]
[TD="align: right"]1/22/2018
[/TD]
[TD="align: right"]12000
[/TD]

[TD="align: center"]24
[/TD]
[TD="align: right"]1/23/2018
[/TD]
[TD="align: right"]12000
[/TD]

[TD="align: center"]25
[/TD]
[TD="align: right"]1/24/2018
[/TD]
[TD="align: right"]12000
[/TD]

[TD="align: center"]26
[/TD]
[TD="align: right"]1/25/2018
[/TD]
[TD="align: right"]12000
[/TD]

[TD="align: center"]27
[/TD]
[TD="align: right"]1/26/2018
[/TD]
[TD="align: right"]12000
[/TD]

[TD="align: center"]28
[/TD]
[TD="align: right"]1/27/2018
[/TD]
[TD="align: right"]12000
[/TD]

[TD="align: center"]29
[/TD]
[TD="align: right"]1/28/2018
[/TD]
[TD="align: right"]12000
[/TD]

[TD="align: center"]30
[/TD]
[TD="align: right"]1/29/2018
[/TD]
[TD="align: right"]12000
[/TD]

[TD="align: center"]31
[/TD]
[TD="align: right"]1/30/2018
[/TD]
[TD="align: right"]5400
[/TD]

[TD="align: center"]32
[/TD]
[TD="align: right"]1/31/2018
[/TD]
[TD="align: right"]2000
[/TD]

[TD="align: center"]33
[/TD]

[TD="align: center"]34
[/TD]
[TD="align: right"]2/1/2018
[/TD]
[TD="align: right"]4600
[/TD]

[TD="align: center"]35
[/TD]
[TD="align: right"]2/2/2018
[/TD]
[TD="align: right"]4600
[/TD]

</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
January
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
February
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
March
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"]2018
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]13
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]16
[/TD]
[TD="align: right"]17
[/TD]
[TD="align: right"]18
[/TD]
[TD="align: right"]19
[/TD]
[TD="align: right"]20
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]21
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]27
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]31
[/TD]

[TD="align: center"]8
[/TD]

[TD="align: center"]9
[/TD]

[TD="align: center"]10
[/TD]

[TD="align: center"]11
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]10
[/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]16
[/TD]
[TD="align: right"]17
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"]18
[/TD]
[TD="align: right"]19
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]21
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]24
[/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]27
[/TD]
[TD="align: right"]28
[/TD]

[TD="align: center"]16
[/TD]

[TD="align: center"]17
[/TD]

[TD="align: center"]18
[/TD]

[TD="align: center"]19
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]10
[/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]16
[/TD]
[TD="align: right"]17
[/TD]

[TD="align: center"]22
[/TD]
[TD="align: right"]18
[/TD]
[TD="align: right"]19
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]21
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]24
[/TD]

[TD="align: center"]23
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]27
[/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]31
[/TD]

</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

Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Creating a Calendar for a Month with tasks
Replies
0
Views
479
anuradhagrewal
A
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
929
Legacy 352679
L
J
  • Question Question
Formula to move information to calendar
Replies
0
Views
461
jc352
J
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
D
  • Question Question
Calculating attendance without using regular attendance formula
Replies
3
Views
509
DarrenK
D

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top