Hello,
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
Please Login or Register to view this content.
Any ideas? Thank you for your help!
Hehe, "opaque" is a great word. Now, what color did you have in mind?
_________________
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!)
I need the pattern style to be opaque. In the "fill" tab in the format cells command, there are different pattern styles you can choose from. I just need one that is opaque and can still see the words underneath.
Sorry. maybe "opaque" wasn't the correct word. lol
With no sample sheet, it's hard to know the best way to advise you.
Can you simply turn on the Data > Filter > Autofilter and custom filter column B by "contains cancel" ?
Then you could simply color the visible rows and turn the AutoFilter back off. That could be put into a simple macro, too.
The sheet has thousands of lines of information, 17 tabs, and it has to be ran twice, maybe three times a week.
I'd like to put it in a macro if at all possible. Wondering how i could go about doing it.
I just recorded a macro....for the type of pattern needed:
Now i just need to combine this with a find "Cancelled" or "Cancel" In columns T and UPlease Login or Register to view this content.
Last edited by NBVC; 09-03-2009 at 10:50 AM.
First, does the Autofilter approach work? If at least one column in your dataset is filled compeltely all the way down, no gaps, then you can autofilter column B by "contains cancel".
If that works, it will make for a very short macro.
A sample sheet always makes this easier.
hmmm...i tried to upload an example file but it didn't work.
If it's too big, zip it first.
GO ADVANCED > paperclip > browse > UPLOAD > submit
Is there a formula that could search for those two words in a sentence in those columns?
Duh, we're talking about formatting a row based on a cell value. Duh. Can't believe I got tunnel vision on the macro thing.
This is conditional formatting. You can set a conditional formatting rule for the row that changes the color if cell B contains the work "cancel".
In this sample attached file, I just used a conditional format formula of:
=ISNUMBER(SEARCH("cancel",$B1))
Thanks for your help!
I seem to only be able to change the pattern just in the cell, instead of the row.
Any suggestions?
Highlight the entire Range of rows. Make sure they are ALL highlighted. Then set the CF based on the primary row you have highlighted.
In my example, I highlighted a bunch or columns from primary row1 down to 10 or so. Then the formula I used had $B1 in it so it locks the column with the absolute $B but uses a relative 1 allowing the formula to change itself as it goes down the rows I have highlighted.
Another way is to get row 1 working. Then use the Format Painter icon to apply that rows formatting to other rows.
Hey,
I want to say thanks again for all of your help. Everything has been really easy for me to understand after your explanation.
For some reason, when i put in the conditional formatting formula for "cancel", it still will not recognize it.
It will recognize it if i put in "Format only cells that contain:", but then it will only recognize the individual cell and not the row. I tried the absolute "$" approach but still no luck.
I will keep trying it out. Hopefully something will turn out.
Please post the formula you used in the Conditional Formatting dialog.
Ben Van Johnson
=ISNUMBER(SEARCH("cancel",$T1))
I need it to search for the word "cancel" in column "T". Once it spots it, I need it to highlight the entire row.
The word "cancel" may be in a sentence as well. I still need it recognized.
The formula works:
CF(test).xls
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks