Hello,
I have an Excell sheet with a lot of years in cells: 2019, 2020, 2021
I also have 1 cell with a date which I change manually, for example 02-03-2019, 01-04-2020
When I change the date, I want to highlight the cells which have the same year.
I tried with conditional formatting, but I do not succeed.
Can someone help me please, step by step.
Thanks
Tom
Date.jpg
Select the range you want to apply with X33 is active cell.
Use conditional formatting with bellow formula.
Please Login or Register to view this content.
❖ Please mark your thread is SOLVED if there has been offered a solution that works fine for you.
❖ If you like solutions provided by anyone, feel free to add reputation by clicking on ✶ Add Reputation bottom left of their posts.
Hi huuthang_bd,
I tried your setting but it doesn't work.
I'm sure it's me doing something wrong.
Here is a screenshot:
The cells are calculated values and at this point the cells with value = 2020 are not marked. I want them to.
Attachment 676839
What could be wrong ?
You want me to upload my Excel file ?
Thank you for the help.
Tom
There are instructions at the top of the page explaining how to attach your sample workbook.
Possibly try this:
=X33=VALUE(RIGHT($A$3,4))
Last edited by AliGW; 05-09-2020 at 05:19 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGW, huuthang_bd,
I uploaded my Excel file (removed the names).
I want to select the cells from S14 to AE114.
If I put a date in A3 (03-04-2020), I want the cells with value = 2020 to be marked.
If I put a date in A3 (17-11-2023), I want the cells with value = 2023 to be marked.
The file is called copy.xslx
Thank you
This works in your sample file:
=VALUE(S14)=YEAR($A$3)
Hi,
I tried your formula, but nothing happens.
I really don't know what I am doing wrong.
- I select my range of cells
- Click on conditional formatting
- Enter the formula and choose layout
Attachment 676857
But the cells are still not in color.
This is the manage rules:
Attachment 676858
Can I post a video ?
Selecting either of the attachments in post #7 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
Attached to this post is a copy of the file from post #5 with Ali's formula applied.
Note that I chose to fill the affected cells yellow to differentiate from the prefilled cells in the range.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hello JeteMc,
Thank you for posting the rule and the excel file.
I don't know why, but when I selected an area a Semicolon ; appeared between the cells.
I changed it into a colon : and now it works fine.
Thank you very much.
Thank you also AliGW and huuthang_bd.
=$S$14:$AE$114
FYI My solution worked fine in your file!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
You're Welcome and thank you for the feedback. Please do as AliGW states and mark the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks