LinkBack URL
About LinkBacks
cell A1 increments daily by 10. currently it sits at 5780.
how do i apply a conditional format so that only the last 3 digits are evaluated. IE when it gets to '*900' it turns amber?
cell A1 increments daily by 10. currently it sits at 5780.
how do i apply a conditional format so that only the last 3 digits are evaluated. IE when it gets to '*900' it turns amber?
Hello
Maybe this CF formula might work if the last three digits are 900 or greater:
=VALUE(RIGHT($A$1,3))>=900
DBY
ok can you explain what the formula means first tho so i can get a better understanding please.
Select the cell or cells where you wish to apply the formatting, then open up Conditional Formatting and apply as in the attached image. Here it has A1 as an absolute reference but you can change that depending on what you require.
CF formatting screenshot.jpg
It uses the RIGHT function to return the last 3 digits in the cell but as text, so the VALUE function turns it into a number to calculate the =>900.
Hope this helps.
DBU
Last edited by DBY; 04-24-2015 at 03:47 PM. Reason: added further explanation
it has helped its introduced me to the right function, thank you, i can now research more to get the desired format i need. I've never really used conditional formatting before and I've kinda threw myself in at the deep end.
Thank you kindly
This will work if the values are in column A.
Select the cells in column A that you want formatted if the right 3 digits make a value of 900 or more.
In Conditional Formatting, New Rule, Use formula enter this formula and then choose the formatting that you want.
Formula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
ok so i selected the cells in D3:D22 in my case and applied the formula =RIGHT(D1,3)*1>=900 and to highlight amber and i got one that was 10899 highlighting and none of the ones that should have highlighted.
this is why it drives me crazy lol
As you selected range D3:D22, then use =RIGHT(D3,3)*1>=900
How can you use Conditional Formating.
--In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
shhhhhh right my god I'm stupid lo. where did you learn this stuff so i can address the root cause of my stupidity lol
can i send you the file and you have a look at it for me?
test.xlsm
so as you can see i have applied your rule to d3 to d22 i intend to do th same with column f and h.
i think i could roughly organise it so that when it goes from 10940 to 11010 by incrementing that i could make the cell then highlight red as it has gone over the 000 mark as such with the right function controlling it.
Quick question, could i use a and input box activex, not a pop up one tho one that alway there, when i type the number of hours in say in this example if i typed in the actual hours being 10999 could i make a macro that will ignore the conditional format for say 200 hours making the fill white then after 200 hours has passed apply the conditional forma again?
**could i use an input box activex**
Now you know how to do this.so as you can see i have applied your rule to d3 to d22 i intend to do th same with column f and h.
Not sure that i get which is the goal here.i think i could roughly organise it so that when it goes from 10940 to 11010 by incrementing that i could make the cell then highlight red as it has gone over the 000 mark as such with the right function controlling it.
You'll need to start a new thread in VBA section for this and pls make sure that will have a better title than this one.Quick question, could i use a and input box activex, not a pop up one tho one that alway there, when i type the number of hours in say in this example if i typed in the actual hours being 10999 could i make a macro that will ignore the conditional format for say 200 hours making the fill white then after 200 hours has passed apply the conditional forma again?
it was a title of despair which attracted you lol many thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks