Alternative to conditional formatting for project management calendar

camascari

New Member
Joined
Jul 19, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Creating a calendar to be shared with multiple teams. I wanted to use conditional formatting to show whether a project was not yet started, in progress, or complete, as well as color-code tasks. However, it was way too many conditions and started crashing my workbook. I'm trying to see if there is an alternative by using VBA and I'm wracking my brain trying to figure it out.

All tasks are listed on a master "task" sheet and pulled into the cells of the calendar using the following formula:
Excel Formula:
=IFERROR(INDEX(Tasks[DESCRIPTION],SMALL(IF(DATEVALUE(TEXT(B$5,"mm/dd/yyyy"))=Tasks[CORRECTED],IF(Tasks[SHOW]=TRUE,ROW(Tasks[DESCRIPTION])-4,"")),ROW()-5)),"")

Capture3.PNG


Would it be easier to format this master sheet then have a VBA copy and paste the task description into the proper date on the calendar? Or to leave the current formula as-is and create VBA to format each cell based on the criteria? (There can be multiple tasks on any given date and there are checkboxes at the beginning of the calendar allowing user to toggle viewing certain tasks).
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,143,690
Messages
5,720,311
Members
422,275
Latest member
Maria95

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