Hello
Please can I have some help. On spreadsheet I have some %'s in column D. What I would like is anything that has 5% or higher to be highlighted which ive done easily enough. But it is also highlighting any blank cells. Is there away to ignore those?
Thanks
Simple AND formula in the CF
=AND(D4>0.05, D4<>0)
Hi,
Add an AND condition to your current CF
i.e.
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hello thanks for your replies. I think im being a bit dumb but how do I add that formula? I was just using the conditional formatting icon.
Select 'Manage Rules' (as you have an existing rule) when you click the Conditional Formatting icon on the Home tab. You'll find the option to edit your existing rule there.
Would you upload your workbook, or at least a cut down copy so that we may see the request in context.Originally Posted by watchthisspace
Hello, thanks again for your help. So ive clicked condional formatting, then edit which just allows me to change the original. so ive tried new rule which then allows me to add another rule selected use a formula to determine which cells to format and add your formula but the blanks are still highlighting.
I'm obviously doing something wrong but unsure what.
Hello ive now attached an example.
Thanks again for everyones help
Hi,
Put this CF in D1
Formula:Please Login or Register to view this content.
Incidentally it's not generally good practice to select or apply stuff to whole columns, particularly now that Excel has over 1m rows. Instead apply to just the rows that are in use.
The column G requirement is confusing. Do you mean to highlight where the cell is EITHER >20% or less than 10%. i.e an OR condition. Clearly a cell can't be both > a number and also at the same time less than that number.
In which case
Formula:Please Login or Register to view this content.
Hello thanks for your help.
With column G I would like it to highlight one colour if its >20% and a different colour if its less than 10%.
Again im doing something as the formulas arent working for me. Would you be so kind to add them to the uploaded spreadsheet so I can then see what im doing wrong?
Thanks
Hi,
See attached
Thanks for all help that was perfect!
Hello
Sorry to be a pain im having issues with getting column G to work. Ive selected G3 to G100 clicked on conditional formatting icon, then new rule and selected Use formual to determine which cells to format. Then added your first formula and selected a format colour and this works great. I then need to add the second rule. So to do that im clicking back on the conditional formating icon going to manage rule and then new rule. Selected Use formual to determine which cells to format, then added the second formula and a different colour. The issue im having is that the two colours arent show. If a cell has any of the criteria is picking just the 1 colour.
Difficult to comment without seeing your workbook and what you have actually done. Are you able to upload?
Hello
Ive now attached it and youll see in column G they are all the same colur. thanks again for all your help.
I assume you want > 20% to be green and <10% to be yellow.
Your Green formula is referencing both >20 and <10. You can remove the <10%, the OR and the associated parentheses
Your yellow formula is good but is referencing <10 twice. Remove the OR and the duplicate and the associated parentheses.
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
..sorry for the confusion
As the Chemist has helpfully pointed out there are redundancies in the formulae I gave. I think it stemmed from earlier when one colour was involved for painting cells both below 10% or above 20%.
Column G yellow should be
Formula:Please Login or Register to view this content.
and green
Formula:Please Login or Register to view this content.
Hello
So I apply the first formula and that works great but when I apply the second one it turns all items green including the below 10%. Ive attached my spreadsheet as there is something im doing wrong, please can you help.
Hi,
Remove the $ signs from the $G$3 reference.
All references should be just G3.
Perfect thank you very much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks