Hello friends,
Please help me out
I have days A7 to A37 and data for each month from Apr-23 to Jun-24 is B7 to P37.
Days B3 to P2 compare with data from B3 to P3.
Only two comparisons are made, April-23 and May-23, color marked manually for reference.
Now my requirement is to get comparative data with cell color for any month or day.
If needed please use helper
Administrative Note:
Is your forum profile showing the Excel PRODUCT that you need this to work for?
Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.
The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.
Thanks.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
SHOW us what you mean - mock it up.
With your sample data for April and May 2023, it's not difficult to auto-fill colors.
However, for other months, I'm unsure if you want to fill colors based on the number of days in which column at row 3?
Please mock up a few more columns.
Quang PT
Hello friends,
First of all very sorry. Really sorry for such a late replay.
I was caught up in some personal issues. Anyway back to the excel sheet problem.
I have done the manual changes as mentioned by Bebo and AliGW so can explain you accordingly
A) Green Highlight
1) A5 "start day" is the starting point where the comparison begins
2) A5 to P5 will input the day for comparison
3) B6 to P6 will calculate and display the data already shown in blue highlighted color
What exactly do I want now (conditional formatting)
A8 to P38 should be automatically highlighted (green manually highlighted) when inserting any day in A5 to P5 (Day 1 to 31).
E.g. Start day (A5) is 5 and B12 to B14 Apr-23 for (B5) 7 in A12 to A14 Apr-23 my green color will appear automatically.
B) Yellow highlight
C1 to F3 yellow highlighted box
Here also from C2 to F2 will insert start day and select month from D1,E1,F1 dropdown then D3 to F3 data will show and highlight yellow color (conditional formatting) in A8 to P38.
Any month and day it should show the data
Please help please and once again sorry for late replay.
Four conditional formatting rules:
For column A:
1. For green: =AND(A8>=A$5,A8<=MAX(B$5:P$5))
2. For yellow: =AND(A8>=C$2,A8<=MAX(D$2:F$2))
For columns B:P
1. For green: =AND($A8>=$A$5,$A8<=B$5)
2. For yellow: =AND($A8>=$C$2,$A8<=INDEX($D$2:$F$2,MATCH(B$4,$D$1:$F$1,0)))
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.
Awesome sir thank you very much.
Conditional formatting is working fine. Even the dropdown conditional formatting works very well,
But once the dropdown (D1,E1,F1) changes the data for that month (D3,E3,F3) also needs to be calculated which doesn't happen.
Sir please provide solution
Make the following modification to the formula in cell D3 and then drag the fill handle to the right:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
hats off to you thank you very much
You're Welcome. Thank You for the feedback and for marking 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