Hi,
I have a series of conditionally formatted cells which turn the text in them to red if the value is less than 40. This works fine.
Occasionally however a value of less than 40 will need to be entered along with the letter 'v'.
ie. 39v
I'd still like this to be coloured red, but it's obviously coming out as black.
Is there a way to sort this out?
edited to add : Im actually using a separate cell to enter the value 40 (as the value can change).
Thanks
Last edited by Barking_Mad; 08-18-2009 at 11:46 AM. Reason: more info
Are they the only options (numeric or number followed by v)?
You can try setting the conditional formatting to formula andDoes that work for you?
Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
That's not quite working, im not sure why....Maybe it's my crap knowledge of ExcelOriginally Posted by ChemistB
Are they the only options (numeric or number followed by v)?
You can try setting the conditional formatting to formula andDoes that work for you?Please Login or Register to view this content.
Ideally i'd like to set the value (ie 40) in another box (as this value can vary and i don't want to have the users to continually change the value through the formula) and then have anything below that value turn red, even if it has a 'V' on the end of it.
I do have another text string, but that is alphabetical only and i can sort that separately.
Hope this makes sense...
Oops, I used the Greater than sign. Assumptions, your range that you are conditionally formatting starts at A1 (change A1 to the first cell in your range otherwise).
your value (e.g. 40) is in C1 (change $C$1 to appropriate cell)
Is that better?Please Login or Register to view this content.
Hmm I have the formula entered into the conditiona fomula box as follows:
=OR(ISTEXT(E$14:$E$152),$E$14:$E$152<$E$153)
Where E14:E152 is the range of cells and E153 is the cell where the value is entered.
The rule is set to show red text, but currently they are all red.......Have I entered the formula wrongly?
=IF(RIGHT(A1,1)="v",LEFT(A1,LEN(A1)-1)+0,A1)<40 should do it
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Yep.Have I entered the formula wrongly?
1. clear out the old conditional formatting
2. select your range E14:E152
3. Go to conditional formatting and select formula
Does that work for you?Please Login or Register to view this content.
When you enter in the first cell of your range, Excel automatically will advance that cell for each cell in your range (e.g. E15, E16, etc)
Last edited by ChemistB; 08-18-2009 at 11:17 AM. Reason: Clarification
Ahh yeah, sorry that works fine. I always get confused about selecting and naming cells in conditional formatting....
Nice one
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks