Forum Discussion
Excel Yearly calender conditional formatting
Select the cells that you want to format conditionally.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Lave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
Enter ="Sat" in the box next to it.
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
Repeat the above steps, but with ="Sun" instead of ="Sat".
That suggests that the name of the sheet is spelled incorrectly. Could there be a space before or after the name in the sheet tab, for example 'Vakantie gastouder ' ?
Hey! I have got a question for you. I know how to do what i want but i kinda want to make it cleaner then how i am about to do it.I wanna be able to automatticly fill the entire calender empty spots depending on a dataset we manually put it. Almost like the vacations.HansVogelaar
I think its easy to understand what i intend to do it.
I could just do =$AO$5 for every monday etc for the week. However there should be a cleaner way to do this. Do u know it? I also need the user to be able to edit 1 monday from the year yet the formula be unaffected. So if they manually entered a number in the calender it would replace formula until the cell is empty.
Solved via PM.
- I haved pmed you the file of data.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
- My bad i am rambling.
So im trying to automatticly fill the sheet with hours of the week. Every week has the same hours.
However when there is a vacation i'd want this formula to output nothing. Empty cell or maybe like a "vrij" in it or something.
I also want the input data i have to not switch days. Now when i switch from year since i made it the same as my calender it switches days meaning Tuesday would be first on the list instead of monday. Yet the hours dont change with so u'd need to manually change the hours for the weekdays. I want this automated or the days to just not move. Which data would u need to try and help me? I'm sorry, I don't understand your description, nor your question.
Alright thank you i think i'd preferr to just tell her to copy from another week with same day etc.
Hopefully last question about this sheet.
I got a weekly hours fillin form but i want when i change years that this automatticly changes with.
2024:2025:
The days already go with. The best wished outcome:
Mon: ActualHours
Tue: Actualhours etc.
Mo-Su.
So if possible i'd like the days to rotate but not switch actual cell data.
the hours get put into the sheet doing:
=AO5, AO6, etc. The yellow filled cells being AO5-11.
Any good way to do this?
Edit: Found another thing i want to force.
When custom formatting is enabled(For vacations etc) i wanna make that cell be empty but have the formula there somehow. I dont think this will be possible without code which would be sad. If thats the case let me know any data u'd need to help me out writing that code if u got time haha.The only way to restore the formula automatically would be using VBA code. But that would require all users to allow macros when they open the workbook.
- That is sad news. Do u know any work arounds to input the formula's data? If not i will have to explain to the user how to put back the formula after which should also be no problem
That's not possible - if the user enters a value in a cell, the formula in that cell is overwritten and cannot be retrieved...