Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question
Can you give me a couple of examples of what this text would look like
Yes... I'll get some text copied and paste it here in a sec.
or I could have you send the sheet if that would be easier
Here is an example of the text in Column F:
nherent component of code 50200, <i>Renal biopsy; percutaneous, by trocar or needle</i>.</p><p>In the Urodynamics subsection (codes 51725-51798), the structure of this series has been changed. The following four codes were either revised or added:</p><p>?<b>51726</b> Complex cystometrogram (ie, calibrated electronic equipment);</p><p><b>51727 </b> with urethral pressure profile studies (ie, urethral closure pressure profile), any technique</p><p><b>51728</b> with voiding pressure studies (ie, bladder voiding profile), any technique</p><p><b>51729</b> with voiding pressure studies (ie, bladder voiding pressure) and urethral pressure profile studies (ie, urethral closure pressure profile), any technique</p><p>CPT code 51726 is used to report complex cystometrogram. Codes 51727-51729 are indented under the parent code 51726. To accommodate these additions, codes 51772 and 51795 were deleted. In code 51729, the service is a combination of the services described in codes 51727 and 51728. Since these procedures include the cystometrogram, it would not be appropriate to report either code 51727 or code 51728, in addition to code 51729.
You can see the 5 digit codes. If they were all highlighted, they sure would be easier for me to spot and then go about my work to verify them.
so there are multiple codes within a single cell
In column I, the same 5 digit codes are supposed to have been copied. But I have to proof that, in addition to verifying the codes in Column F
ok, is all of that text within a single cell?
Yes, there are multiple codes within a single cell. Some are single codes, some are a range, e.g., 51727-51729. But if both the codes are highlighted, I can easily see the range and work with it.
well if they were all numbers, it certainly would be possible to to separate all alphanumberic from numbers in a cell
I would like the codes that appear in Column I to be highlighted as well, so I can spot them more easily as I move through the spreadsheet.
but it is not possible to hilight text within a cell
so its either hilight the whole cell or not
what is possible is to search a cell for content
inotherwords we can ask excel if the number 12345
is somewhere in the cell
Oh... do you mean it is not possible to highlight just numbers in a cell?
I mean just highlight 5-digit numbers?
ok it is possible to change the font color
of all numbers in a cell with VBA
but not the background
so for example
you could have all numbers in red
and all letters in black
or something like that
a macro would have to be written to go through all rows and do this
That would work if we could just have all 5-digit numbers in a different color text. There are too many other numbers like in dates and things that would muck it up.
it would be complicated
but it may be possible to count the number of numbers in a row
Does a macro work on an entire spreadsheet at one time? Or would I have to apply the macro on each individual line?
the macro would run through all rows in the sheet
in specified column
I'm not certain I understand "it may be possible to count the number of numbers in a row" .... do you mean apply the macro to change text color only for numbers with a count of 5 ?
something like that
so i would check is it
obviously only the 12345 would be changed to red
and when you show "12345" that doesn't mean I would have to run the macro for every code possible. It means that it would change the text color for any series of numbers that has 5 digits?
Oh... "1234a" would accommodate an alpha-numeric code 0017T, and "12345" would accommodate just a 5 number code? Would that run in the same macro?
the 4 digit and alphanumerics might casuse a problem
How does this work? You would give me the macro and tell me how to run it in my spreadsheet?
can you send me your sheet?
it would be easier just to write and test it on your data
Can I just send a limited number of lines rather than the entire spreadsheet? My client paid for the content and I don't want to distribute the whole thing.
i wouldn't distribute it
you can send me a few lines
then copy and paste
your data back in
you do realize it wont actually be higlighted, just the font color changed
How do I send?
click the paperclip icon at the top of the chat window
where you type
but does excel files
Yes... I understand the font color only will be changed. I would like dark green if possible, rather than red. Because the ones I have to identify as not good codes I want to change text to red.
but just put whatever color you want in your example file
Ok... doing example file now.
Did this work? I didn't use red, I used the color to the left of red in the Standard Colors choices.
let me check it
In the sample, I only changed the text color for one of the codes in that cell just for illustration. But I would want all 5 digit codes to have text color changed. If we can't get the codes with the alphanumeric, so be it
So text will change for both Columns F and I
so column I should be all that red color
Yes. Each number in Col I is separated by a comma... don't need the comma in a different color.
well the comma doesn't matter
i mean you could just do column I yourself
let me get the macro to do the F column
Good point! I guess I was originally thinking maybe there was a way to show any codes that got into Col I that were not in Col F but I don't think that happened.
it's mostly that some of the codes in Col F did NOT get to Col I
well, maybe it would be easier to just populate column I with any of the 5 digit number sequences that are in coloumn F
I didn't think the macro could actually also copy codes to Col F that weren't already there, right?
if I am chaning the colors, I could just as easily copy them to another column
at that point
the only problem with that might be the ranges
Oh, but the issue is that I have to report back ONLY the rows that DID NOT have all the codes in Col I.
well I can count how many comas are in coloumn I
and how many of these 5 digit sets are in F
oh right.. the ranges. If a range didn't make it to Col I, then I have to go elsewhere and verify all the codes in that range and add them in one at a time
You mean ranges?
lets just stick with the coloring for now then
we can apply other macros later once you get your data straitgthened out
so the way this site works, is that once I provide this solution
and we complete this transaction
then you will have to ask me additional questions for additional functionality
i give you the basic macro
once that works
you can come back to me for an addtional question and ask to add other functionality
or do a different macro
Ok... so will you also tell me how to "insert" the macro? Is that part of this first round? I think I've done them before, but if it's straightforward, I can figure it out.
i will help you insert it
So I understand that you will give me a macro for this first functionalty - to change font color of 5 digit codes. Then I I want to do anything else, that starts a new ticket so to speak...
my thought would be that you would use this sheet, then just copy your data from your other sheet and paste it in
i think that would make it easier for you
and yes to the above
Ahhhh... good thinking!
I meant "Then if I want to do anything else." I typed "Then I I want to do anything else..."
But we are on same page.
i think so
I pay for this first macro and other requests for additional functionality are extra.
ok, give me a little while to work on this one
ok... I have to make a phone call, but I will leave this page on my screen.
Briliant- worked for the first cell
now lets see if I can apply it to all the rows
Have to make 2nd phone call.
Just so you know I didn't go anywhere.
okay squashing a few bugs
for some reason I am getting false positives and I can't firgure out why
are false positives text that changed color but are not numbers?
im just trying to correct that
a lot of it? Because if just a little, I can still see the numbers easier.
It's just been so hard to proof trying to find the numbers amid all that html code stuff.
give me a minute I have an idea
looks good not
just my stupidity
you see I was resetting the counter after it reached 5
by was not just counting consecutive numbers
but something like
was increasing the counter by 2
no stupidity if you figured that out.
do you know how to run a macro
or do I need to put a button somewhere for you to run it?
I did one once a few months ago.
so this macro will determine how many rows you have
Is it a matter of me launching the macro thingy and pasting your code?
you dont need to paste anything
just enable the macro
then copy and paste your data in
to this file
its easier i think
why do I care that it will determine how many rows I have?
Oh... it will go out and look to see how many rows and then run for all of them?
whehter you have 70
it doesnt care
here is the file
let me know if you have any problems questions or concerns
To pay, you would rate with the smileys beneath this chat
---------------------------------------------------------------------------------------------------------------if you have any questions in the future you can visit my profilehttp://www.justanswer.com/computer/expert-1expert-2rulethemall/and ask a question right in my box,also you can put "PC Guy only" in your question title if you want to get to me.
So, I go to Developer tab, click Macros and a list of one pops up, then I click run?
should only be 1 in the file
Do I test it before I choose the smiley?
remember you have to press the yellow enable macros button when you first open the file
test it first please
so I can correct any issue
I tried to download it and a box popped up that says
"Excel cannot complete this task with available resources. Choose less data or close other applications. Continue without Undo?"
So do I have to close a bunch of stuff or restart my PC?
di you actually open the file
and you got that message?
or did that happen when you tried to run the macro
When I clicked on your link, it led me to a web page and an option to "download" and that's what I clicked.
Then I got the box.
try saving the file to your hard drive first
then run it from there
your computer may be trying to run it off line for some reason
you should have the option to save
and not open
when you click the download link
I did save file and then went to downloads and clicked on it, but got the same box.
you may have to reboot.
bookmark this page
so you can get back to me
ok. Gosh, what if it doesn't go back to you?
did you give your email?
when you asked the question
if so this site sent out some emails
to the question
but you can bookmark this url too
I see it... I can go to email and click on View Your Answer and I'll get back to you if bookmark doesn't work?
yes, I would assume that would work
I will be around
if you need me further
You would have to come back to rate and pay me anyway
I just closed a bunch of stuff and will try to open before I reboot
That's why I want to make sure I can get back to you
yes... but I had word and acrobat pro and other apps open so I just got everything saved and closed out and now trying again
Going to have to reboot. I've got the link
Be right back
I rebooted... and got this back up first. Now I'm going to try and open file.
well let me know when your ready
I'm smiley clicking now.
you know how to get back to me if you need me again
Thank you so so much. Yes. I've got the contact info. It's great to know who to go to!
PC Guy only it is.