Ask Your Question

help with formula on conditional formatting with Icons

asked 2020-09-18 21:17:50 +0100

clos911 gravatar image

updated 2020-09-18 22:24:21 +0100

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

image description

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-09-21 00:27:04 +0100

LeroyG gravatar image

updated 2020-09-21 00:37:55 +0100

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 (Correct answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more


Bravo @LeroyG !

mgl gravatar imagemgl ( 2020-09-21 10:24:32 +0100 )edit

answered 2020-09-19 19:07:14 +0100

mgl gravatar image

updated 2020-09-20 00:18:46 +0100

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

edit flag offensive delete link more


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

clos911 gravatar imageclos911 ( 2020-09-19 20:20:24 +0100 )edit

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

mgl gravatar imagemgl ( 2020-09-20 21:02:57 +0100 )edit

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..

clos911 gravatar imageclos911 ( 2020-09-22 02:37:13 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-18 21:17:50 +0100

Seen: 44 times

Last updated: Sep 21