The conditional highlighting is not giving any response even for simple conditions like : highlighting a cell into red containing a number that matches a number in a in another cell. I have tried this several times even by formatting all the cells into numeric and then executing the command menu. Please help
iPhone 11, iOS 17
If either of those cells (C4 or C61 in your screenshot) are calculated by a formula, it is possible that the result is not exactly 1271.65. You may have the cells formatted to display two decimal places but that does not round the actual number in the cell, it just shows it that way. If you use ROUND in the formula it will round the number.
=ROUND(your_formula, 2)
Alternatively, you could do "Text is" instead of "equals" as the conditional highlight rule.
Note that 1271.654 (which rounds to 1271.65) and 1271.646 (which also rounds to 1271.65) will be the same for this highlighting rule.
You will have to decide which is better for your data and the highlighting rule. Do you want/need the values to be the exact results of the math being done (but shown with two decimal places) or do you want them rounded to the two decimal places? Your decision.
If either of those cells (C4 or C61 in your screenshot) are calculated by a formula, it is possible that the result is not exactly 1271.65. You may have the cells formatted to display two decimal places but that does not round the actual number in the cell, it just shows it that way. If you use ROUND in the formula it will round the number.
=ROUND(your_formula, 2)
Alternatively, you could do "Text is" instead of "equals" as the conditional highlight rule.
Note that 1271.654 (which rounds to 1271.65) and 1271.646 (which also rounds to 1271.65) will be the same for this highlighting rule.
You will have to decide which is better for your data and the highlighting rule. Do you want/need the values to be the exact results of the math being done (but shown with two decimal places) or do you want them rounded to the two decimal places? Your decision.
Finally, I could discover my solution to the problem. While selecting the cell which has to be matched to the cell to be highlighted in the data, the row and column of selection cell has to be preserved (i.e. the cell has be $C$4). Then it works fine.
Thanks to all who have given their valuable suggestions to my problem.
N,
The content of the cell that you have referenced as the Condition apparently must be entered directly, as in Manually.
If there is an Expression in the referenced condition cell, there will be no match. If the content of the condition cell is the result of an Edit > Paste Formula Results, there will be no match.
I just determined that by experimentation. I would welcome someone confirming that.
I read in another thread's answer some time ago that only constants are accepted in the Condition field. Apparently this is one way that requirement is manifested.
Jerry
I didn't save my file.
First test was two unequal numbers in two different cells. 123.456 and 123.464. Both cells formatted as currency so both look like $123.46. First cell was B2. Conditional highlight in the other was "equal to cell B2". Conditional highlight did not highlight it, as expected. Changed the rule to "text is cell B2" and it highlighted.
Second test used the same two cells but they had formulas in them. One formula was =C2+D2/1000 where C2 was 123 and D2 was 456. The other formula had 123 and 464 as the input. Tried same highlighting rules with the same results as above.
That was the "text is" test. The I put ROUND around the two formulas. The "equal to" rule worked correctly and the "text is" rule also worked.
Can you please give some more exact examples, or screenshots?
I use conditional formatting all the time and have created some rather intricate solutions using it before in numbers. Have not had your issue yet. I just tested my most complex one and it is working fine on my iPad.
jason
I have attached few screen shot to explain my problem: In the above image you will notice that cell No C4 is containing a figure ₹1271.65. I want to highlight the cell below which matching the above number. The matching figure is appearing in cell No. C61.
In the next image (attached below) I have shown the condition that I have inserted to get the result.
After having give the above condition to highlight the cell with orange colour that matches the figure in Cell no C4, but the app is not highlighting the cell no. C61 with orange colour. This can be seen in the image attached below:
I shall be glad if you can give some resolution to the above issue.
Thanks
Ok. Nice screenshot. Now what is the part that isn’t working for you?
Badunit,
Neither of those alternates worked in my testing here. Curious how you are testing.
Jerry
Thanks - I'll see if I get that here.
Jerry
I apologize, my phone didn’t expand to show all your work there. You did give enough, I just couldn’t see it.
hope badunit was able to help.
jason
Conditional High lighting of cell
