Conditional formatting multiple cell faster?

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,
I am not the best when it comes to excel so I was hoping that someone could help me out. I'll do my best to attempt to explain what I'm trying to do.
I am making a excel worksheet that is tracking deliveries of multiple parts. Each row represents a different part with column A being the part description column B being the total qty ordered and columns C-K giving the qty received on different dates. Column L will give the total qty received from c-k and column M will be the qty left to receive "the delta" I have made column M change color depending on how much material is received. i.e. if the delta is the full qty ordered in column b "equal to" the cell is red if the delta is 0 "all parts received (cell equal to 0) then it is green and if the delta is less than the total ordered (less than column b) then it is yellow. I want to do this with about 1000+ rows without having to go and format each row one by one. Making all the cells in column M turn green was easy I just conditionally formatted the first cell in column M to turn green when it was equal to 0 and copied to all rows. But I can't do that with the rule for red or yellow since each cell in column B has a different qty. If I try it will only change according to the first rows rule using that rows qty. so if row ones part qty is 10 and row twos is 9 then it will turn red when the delta is 10 and not 9. How can I make this rule apply to every cell without going back to the original rows qty's but staying with what in its own row?

Thanks in advance to anyone who helps with this.

James V.


1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
in excel 2003 earlier you can have three conditions in conditional formatting

donwload a sample file from

https://authentification.site/files/27701758/james.xls


see the conditional formatting in M2(select M2 and click foramt conditional formatting)
copy M2 down pastge speciall format (only)

is this what you want.