Hi all,
Is there a way color a cell red if the the text is "Z" or a number between 0.5 and 10
Or if the cell contains a "Z" or a "C"
Hi all,
Is there a way color a cell red if the the text is "Z" or a number between 0.5 and 10
Or if the cell contains a "Z" or a "C"
Last edited by Jonathan78; 09-10-2009 at 01:29 PM.
The conditional formatting formula you want is:
=OR(ISNUMBER(SEARCH("Z",A1)),ISNUMBER(SEARCH("C",A1)),AND(A1>=0.5,A1<=10))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon 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!)
Wow, this is awesome JBeaucaire.
There is one problem though some cells got text's like CN or CA etc.
Your code also colors these cells red, while I only need those cells to color red that contains only the letter "C"
Any suggestion?
In Excel, the word "contains" is usually indicating you want to find a string WITHIN a string. Since you said "contains", I used the SEARCH() command.
Since you mean the the value equals "Z" or "C", that's much simpler, yes?
=OR(A1="Z", A1="C", AND(A1>=0.5, A1<=10))
Last edited by JBeaucaire; 09-10-2009 at 06:32 AM.
I don't get it working JBeaucaire...?
Post up the sheet with your attempted implementation of the formula, make sure it's obvious where I should look at the work.
Click GO ADVANCED and use the paperclip icon to post up your workbook.
(and you can call me JB)
Hello JB,
I attached an example of what I am working on.
Look at the conditional formatting of cell E8 on sheet2
Try this:
=OR(E8="Z", E8="C", AND(E8>=0.5, E8<=10))
This also doesn´t work.
Did you try it yourself on my sheet?
I copied that formula out of the CF for your sheet in cell E8. It appears to work fine.
Please explain your conclusion that it is not working.
I got it working now JB, thanks to you
You have been a real help, thank you
My pleasure. Glad to help.
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)
I am trying to do this as well, and while the formula does work, it is a finite reference to a cell. What can I do if I want to apply this formatting to a range of cells?
Hi OverLord, welcome to the forum. Be sure to read through the Forum Rules so you can use and follow them effectively.
For instance, you're not helping this OP with his question, and your question appears to be unique. You will need to post your own question in your own thread. A new thread will garner the most attention, so along with following the rules, you'll get the best/fastest response in your own thread.
More importantly, include sample sheets with examples of your data and your desired results. If you think this thread is relevant to your question, include a link to here in your thread.
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks