I'm trying something which seems simple but can't for the life of me find a solution.
I'm creating a Holiday planner for our team (5 of us).
So each month has the Weekends highlighted
What i'm trying to do copy the same background color assigned to SAT and SUN to the 5 rows below.
Basically something that looks like this
eg
A1 = color red
B1 = same color as A1
So if color of A1 changes to Blue
B1 should also change to Blue (automatically)
Thanks for your help.
lx
Welcome to the Forum!
What causes the color of A1 to change? Is it based on data entered on the worksheet, or is it manually changed?
There is no built-in method in Excel to detect the background color of a cell. You have these options:
- If the background color of A1 based on some data value, use conditional formatting to color any cells that are affected by that value
- Write a user-defined function (a UDF, which is a kind of macro written in VBA) to return a code for the background color of A1, and use that in conditional formatting of the other cell that is to change color based on A1
- Write VBA to update cell colors based on your criteria
When using VBA, any user will have to allow macros to run in that file for colors to update.
thanks for the reply.
A1 is actually a date
so if A1 is a SAT or SUN it gets a grey background color (conditional formating)
It will change depending on the YEAR
so the 4 rows of cells bellow also need to change to grey
So i guess without a macro this might not work.
Sheet attached just incase.
It's very simple with conditional formatting, now that I know what you're doing. I removed your existing conditional formats and added in three new rules to cover your three calendars. [longxin=conditional formatting=HolidaySheet_Template.xlsx]
6String. thank you so much!!
@6string
I have another little question and can't figure out how to do it.
So the last sheet of this workbook will have the total holidays taken over the year.
But below i would like to have a list of holidays (public holidays) that occur every year.
Most holidays would fall on the same date every year but if they changed (Easter/Good Friday etc).
So i created a little table at the end of the worksheet.
My question
is it possible to highlight the days in the 1st 4 sheets with ref. to the dates on the last sheet.
so all holidays for that year are highlighted.
Only question is what happens to a holiday that falls on a weekend?
thanks!
sheet attached. HolidaySheetCalculations_template.xlsx
[longxin=conditional formatting for dates=HolidaySheetCalculations_template.xlsx]This approach names the range of holiday dates as HolidayLookup and uses that in the conditional formatting formulas. I have used red fill for holidays; that's easy to change to whatever you want.
I have given the holiday highlighting priority, so if a holiday falls on a weekend it turns red, overriding the gray. (Formatting is evaluated in the order shown in the list of conditions, and you can change the order.) This is easy to change, depending on what you want.
Another option is use a bold red font for holidays, so it wouldn't collide with using gray fill for weekends.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks