help with formula on conditional formatting with Icons

[clos911]

I am trying to apply conditional formatting to my D column starting at D3 down which is filled numbers. I have numbers in my J column as well (that column does not need conditional formatting).

I would like the D column to have the green smiley when the number in my D column and the number in the J column are less than 512 when added together.

When the D column added with the J column is more that 512 when added I would like it to have the red face applied.

I am not sure if I am on the right track with my formula so any help would be appreciated. Thanks

[LeroyG]

Add a column with the formula =D3+J3 (fill down), and apply the conditional formatting to these cells; choose value, instead of formula next to the conditions.

If you don’t want to see the results of D+J, see related answer.

---

Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

Check the mark () to the left of the answer that solves your question.

[mgl]

Bravo @LeroyG !

[mgl]

Hello @clos911

The formula you need is the one that provides a figure to test against the content of the tested cell.

Note the comparison sign between the icon and the formula. Very explicit

Try =512-J3 for the orange icon and =512-J3+1 for the red icon. When D3=512-J3 => D3+J3=512, and so on…

UPDATE since your comment :

Yes, apparently, the criteria have to be set for each cell separately. So in D3, provide for a conditional formatting based on the above mentioned formulae. Then, make a second conditional formatting for D4 with the updated formula : =512-J4 for the orange icon and =512-J4+1 for the red icon. And so on.

Be sure to remove the previous conditional formatting by Format>Conditional>Manage, then you select the interesting one, named by its range and you either (delete it and replace it by the 6 ones you need) or you update it by updating its Cell range and create the 5 other ones afterwards. Do not leave several conditional formatting for the same cell : only the first one will be applied.

If you have only 6 cells, this might rapidly be solved.

If this could help, please find herein what I managed to make.

Kind regards, Michel

[clos911]

thank you for the response, unfortunately the formulas did not work for me, all cells had red icons when formulas were inputted…

[mgl]

Hello @clos911

Any success with the updated method ?

If you have a large number of cells concerned by conditional formatting, you may try to develop a macro for this. Have a look at this post.

Kind regards, Michel

[clos911]

Thank you, I do have a several cells that I would have this applied to, up to 2000+ rows. I set the conditional format range as D3:D1048576 for the D column…