Hi all,
I need a formula to paste into conditional formatting that will find any cell in the worksheet that has the same date, even if it is in a different format.
Cells B9, B10, and B11 have dates formatted as dddd, mmmm dd, yyyy. From say A15:D1999 the cells have data values that return dates formatted as dddd, mmm dd, 'yy.
If I could have a vba code as well that would be great.
Sorry, I cant paste a sample sheet as I am typing this on a small keyboard and no excel
Thanks
Hi,
Without seeing a sample of what you are working with, it is difficult to be certain that this will work, but it definitely worth a shot. I would try the formulaThis will return a value of TRUE for all cells in column A that the rule applies to that contain the same date as cell B9 (regardless of their date format). You can use a similar formula for cells B10 and B11, or if you want to check against all 3 cells at the same time, useFormula:
Please Login or Register to view this content..Formula:Please Login or Register to view this content.
I could also do the VBA solution for you if you are still interested after seeing this solution, but I will wait for your response first, as something this simple may change your mind.
Hope this helps
Last edited by ajryan88; 08-23-2013 at 01:52 AM.
1) Highlight A15:D1999
2) Apply this CF formula to the whole range at once:
=ISNUMBER(MATCH(A15, $B$9:$B$11, 0))
How a date cell is FORMATTED does not affect the underlying numeric value in the cell, just how it displays to your eye. So the number should match regardless of the formatting. Else your data isn't what it appears to be.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi and thank you ajryan88 and JBeaucaire:
I tried both options and neither worked - sorry. I think it must be my data as JB said.
I have pasted a sample file. So in sheet Final, B11:B13 (I did change the parameters to match the cells), are blocked. But cell A21m A23, and A32 do not highlight.
Can you please tell me where I am going wrong?
A date is a date, no matter how it is formatted - it is still a 5-digit number representing how many days have passed since 1/1/1900. So if your "dates" are not recognising each other, then probably not all are actually dates, but text that just looks like a date. dddd, mmm dd, 'yy may not be a real date - you can test with =isnumber(cell-ref) FALSE is text
You will need to convert them, but it will depend on exactly what they look like for that
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
So how many digits is the number representing the date 1/1/1900?Originally Posted by FDibbins
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Cells A21:A23 have text in them. Remove all the other text and put a valid DATE in those cells, formatted to appear the way you want, and the CF can do its thing.
Just 1, Biff, just 1
lol
Hi All:
An unexpected criterion has thrown off some formulae. The dynamic range of holidays and vacant dates is now in worksheet Vacant and Holiday Dates. Prior to this requirement, only the holidays were a criterion, which was originally in sheet Guide column J, The formula seems to work, however, if it could be checked that would be great.
Since this workbook has a relatively large number of formulae, all the workbook formulae need to be locked and hidden but allowing the rows in sheet Schedule A14: D163 to be available for selection (copy and paste), as well as on Sheet Guide B2-B12 for data entry.
The “click me” macro enables the range A14:D163 to be copied and paste special values and format to sheet schedule range A14:D163.
Which leads me to my question: I am still not able to conditionally format (cf) the compulsory dates, from sheet Guide B9:B12 in the data returned in the sheet End, copied range A14:D163 or in the returned data of Sheet Guide range A”D163. If there is no need to copy and paste to sheet End in order to keep the formulae available for cf, that is acceptable only please change the vba. It is essensstial that the compulsory dates are highlighted in the returned data whatever the format. A new sample file has been attached.
Thank you and sorry for the lengthy post (better to be detailed!).
Hi All - could you please look at my last post. I am hoping someone will have a solution... thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks