conditional formatting two cols such that both cols get same color based on a sum

**welchs101** · 11-17-2011, 11:33 AM

See enclosed file.......it may help explain what i am trying to do.

Basically, i have two cols of values.......say it is Col A and Col B.

if the sum of the values in ColA + ColB are less than or equal to some limit.......say UL then make BOTH cells in A and B "green".

Also, if the sum of the values in ColA and ColB are greater than some limit.....say UL then make BOTH cells in A and B "red".

I can sort of get it to work (see enclosed file)....but if A1=2 and B1=3 and UL is 4 then only A1 turns red and B1 does not change. see enclsoed file.

**Fotis1991** · 11-17-2011, 11:56 AM

Hi

If I understand correctly your goal, take a look at the attachment

**welchs101** · 11-17-2011, 12:07 PM

thanks. based on your inputs i was able to come up with the following. which works for formatting a large column of values.

in the conditional formatting do the following:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Fotis1991** · 11-17-2011, 12:12 PM

Good job!!

Everything OK, then..

**welchs101** · 11-17-2011, 12:30 PM

well, almost..............

if i need to create a new thread for this i will.........

I forgot i wanted to add a "third color" ..........amber

Consider the following:
1) if (sum(a,b) is greater less than or equal to the UL) and if(sum(a,b) is greater than or equal to the LL then make color amber.

LL<=sum(a,b)<=UL

2) if sum(a,b) is greater than the UL then make red

sum(a,b)>=UL

3) if sum(a,b) is less than the LL then make green

sum(a,b)<LL

for some reason i am having a tough time getting it to work.

**welchs101** · 11-17-2011, 12:34 PM

here is the file i have now.........still cant get the amber to work.......any thoughts......i think its the equation/formula i input for amber but i am not sure

**Fotis1991** · 11-17-2011, 12:36 PM

What is LL??

**welchs101** · 11-17-2011, 12:40 PM

ok i think i got it.......thanks again for your help......never would have gotten on the right path otherwise

see enclosed

**Fotis1991** · 11-17-2011, 12:56 PM

OK! Isaw it!......

I think we did it!

**Fotis1991** · 11-17-2011, 12:58 PM

Glad to helped!!

conditional formatting two cols such that both cols get same color based on a sum

LinkBack

Thread Tools

Rate This Thread

Display

conditional formatting two cols such that both cols get same color based on a sum

Re: conditional formatting two cols such that both cols get same color based on a sum

Re: conditional formatting two cols such that both cols get same color based on a sum

Re: conditional formatting two cols such that both cols get same color based on a sum

Re: conditional formatting two cols such that both cols get same color based on a sum

Re: conditional formatting two cols such that both cols get same color based on a sum

Re: conditional formatting two cols such that both cols get same color based on a sum

Re: conditional formatting two cols such that both cols get same color based on a sum

Re: conditional formatting two cols such that both cols get same color based on a sum

Re: conditional formatting two cols such that both cols get same color based on a sum

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions