I've set conditional formatting to color cells containing a specific value. I select "Highlight Cell Rules" then "Equal value" then set value to 1. Some of the cells with value "1" get the highlighting but some do not. I've applied the rule to cells $A$16:$Z$21 - there are 9 occurrences in the range with a value of 1, however only 6 of them are highlighted. I have attached the workbook.
Thanks for any help.
Some of the values are text, not numbers. The text "1" is not the same as the number 1.
If you don't see the green warning triangles in some of the cells, click File > Options > Formulas and in the section for Error Checking Rules, tick the box for "Numbers formatted as text or preceded by an apostrophe"
Now you will see all number stored as text marked with a green triangle and you can fix the problem.
may be your enteries are pretended as TEXT
go to conditional formatting and use custom formula as =(A16+0)=1 then select the range you want to apply CF. click ok
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
That will fix the symptom, but not the cause. The problem still exists. It's better to fix the underlying cause, so it cannot create more symptoms.Originally Posted by hemesh
Agreed teylyn
Hello teylyn, thanks for your quick response, however when I select the cell and select "Home" on the ribbon then in the number tab I select "Number" but it doesn't change and the green triangle still appears in the cells?
Thanks Hemesh, I will try that if all else fails - teylyn recommends changing the cells format to "Number" or "General" but I'm having a problem still.
It doesn't work because some numbers are stored as texts.
Changing the cell format will not change the underlying value. If the number is stored as text, it needs to be re-entered as a number. Format is just decoration. Value is king.
Hi CARACALLA,
I've tried changing some cells with the green triangle to "Number" and when I check after by highlighting one of the cells and selecting "Home" on menu the "Number" in sub-menu it shows it as a "Number" - but the green triangle is still there and the cell is not selected under the conditional format I'm using.
Do you have a sample workbook showing the issue?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
teylyn,
That was the key, I didn't realize that was necessary, I re-typed them and it works perfectly.
Thanks so much for the help! I will close this post now
Yes, I attached it to the post, however the issue is resolved. Some of the cells were text and I had to change them to "Number" and re-type them to set the underlying value correctly (Per teylyn). thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks