So I have this grid. I was trying to get a conditional formatting to work by just typing in the dates of the federal holidays I want to shade but no matter what I did, 12/28, 12/28/2014, 12/28/14 (just as an example day) it wouldn't highlight. I was using the "cell contains specific text"
Is there a different way to make it so the holidays are highlighted? In an ideal world, I'd be able to change the year and it do it automatically.
You didnt provide your table of holidays, so I made 1 up and put it in R6:R14
Then...
Highlight the range you want to apply the CF to
Used New Rule/Use Formula
copy this formula in...
=VLOOKUP(B6,$R$6:$R$14,1,0)
Format Fill as required
Oh and by the way, check your CF rules, you have them repeated multiple times
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi,
you could list in a range (r1:r100) holidays and apply a new rule to activate conditional formatting
=MATCH(B5,$R$1:$R$100,0)
Just for sharing some ideas
Edit: sorry Ford, I did not mean to overlap (same approach)
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
hahaha canapone, we both used exactly the same column for the search, and you didnt "overlap, we posted the same timebesides, its always good to see different approaches
and by the way, I know Im 12 too late, but congrats on reaching the 1K mark !!
Ciao Ford,
as you can read I'm not sure if CF must be disturbed to highlight numbers or dates themselves
(Forgive my low profile English )
CF works on a TRUE or FALSE answer. So if my vlookup() or your match() returns an error, that is treated as FALSE, any "answer" will be true
Thanks for the feedback
Thank YOU!
Quick followup...I fixed all those conditional formatting (i'm learning and didn't realize how messy it was!)
Something I'm trying to figure out how to do, but not even sure how you'd call it. Is I want a drop down in like A1 that has years, 2012, 2013, 2014 etc. And depending on which year you select, a different date will pop up in cell, say B1. How would I go about that?
To take it a step further I'd actually like to have 2 cells generate specific text when a year is selected. So to be clear, I'm trying to get it to say a specific text, which just happens to be a specific date assigned to each year. 2014 would be 12/28/13, 2015 would be 12/27/14, randomness like that.
Would I use conditional formatting?
For the drop down, you would use data Validation.
Click Data tab, data tools/Data Validation. Select List under "allow" and enter the years you want, eg 2010, 2011, 2012, 2013, 2014.
to pull text based on what date is selected, again, you could put a small table together with date/text, then use vlookup to return the text
Ok so I was able to do the list fine but I'm at a stump on how to incorporate the vlookup, would I use conditional formatting?
I've attached a sample of what I have so far, if you look in AD you can see what I have as the list and then the to the right is what I want to reflect when that year is chosen and where I want that to be changed
You guys have been great here, I have learned a crazy amount
Ok try this for B4...
=VLOOKUP($B$2,$AD$5:$AF$8,2,0)
and then this for A2...
=VLOOKUP($B$2,$AD$5:$AF$8,3,0)
Awesome, that worked!
You are a gift to this site
Glad to help, and thanks for the kind words
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks