Conditional formating for a range of cells

[Guest]

Hi,
I am trying to make an inventory sheet that will automatically hilight low
levels:
Each product has values in columns A through E (representing 5 days). Cell F
contain the requested quantities.
What I am trying to do is to change background colour of the cells that will
sum the requested quantities.
For example:
A B C D E F
5 3 3 4 2 10

A, B and C should be hilighted (5+3+3 = 11, 11>10)
Is there any way I can do this with conditional formating? or in another way?
I have about 500 products (rows) that I need to do the same function to.

Thanks,

 

[Guest]

Hi Karim,

You can use the conditional formatting feature. However, when using it,
select Formula as a condition but you must make use of some Absolute
addressing in the formula or you will have problems. For example if you want
to apply conditional formatting to cells A1, B1 and C1 if the sum of those
cells is > 10, then you need to select the three cells and in conditional
formatting the formula needs to be entered as follows:-

=SUM($A1:$C1) > 10

Note: only the columns have absolute addressing.

The above format can then be copied down the rows using copy-> paste
special-> formulas. As a test, if you select cells A2:C2 and go into
conditional formatting then you can see the formula becomes =SUM($A2:$C2) >
10.

Now you ask why we need absolute addressing on the column. If you select the
range A1:C1 and go into conditional formatting and only use relative
addressing. That is you enter the formula as =SUM(A1:C1) > 10 and apply it
then just select cell B1 on it's own and go into conditional formatting
again, you will see that the formula is now =SUM(B11) > 10 which is not
correct.

Hope this helps. Get back to me if you still have problems.

Regards,

OssieMac

 

[Guest]

Hi again Karim,

If you are comparing the sum of the cells to the cell in column F then the
formula in the conditional formatting will be:-

=SUM($A1:$C1) > $F1

Regards,

OssieMac

 

[Guest]

Hi Ossie,
Thanks for your reply, but this is not quite what I am trying to do (I
didn't explain it well). Your solution would work if I know the content of
the cells before hand, but what I am trying to do is to highlight the cells
as I update the inventory (cells A to E) and the requirements cell (F)
To generalize the criteria, what I am trying to do is something like this:
If $F1 >0, shade $A1 and continue, else stop
if $F1>$A1+0, shade $B1 and continue, else stop
if $F1>$A1+$B1, shade $C1 and continue, else stop
if $F1>$A1+$B1+$C1, shade $D1 and continue else stop
if $F1>$A1+$B1+$C1+$D1+$E1, shade $E1 else stop

like this, anytime I update the cells content they should change. Also, this
should shade partially used cell (if the sum of two cells won't make it and
the sum of three cells is bigger than F1)

Is this still doable with conditional formating or I have to write a
subroutine for it? and how?
Thanks alot

Karim

 

[Dallman Ross]

Karim,

I've read what you wrote below and what you wrote originally,
but I can't quite make them conform with each other. Quoted far down
below, you say you want (for Row 1) all columns before column F
highlighted if the totals in column A through that column exceed
column F.

In your most recent reply, however, you are saying to "shade"
earlier columns whenever F1 is bigger (as opposed to smaller).
These two ideas seem at odds with one another. Which do you want?
By "shade" do you mean "highlight," which you used earlier, or its
opposite?

I thought from reading your original that I understood your
request. But your further, "better explained," post makes it all
less clear to me, I'm afraid.

I will propose what I think you want based mostly on your
original request.

Format cells A1 to E1 conditionally. Select "Formula Is".
Put this in as the formula:

=AND(SUM($A$1:$E$1)>$F$1,SUM($A$1:E$1)-E$1<=$F$1)

Make the fill color for that condition your preferred highlight
color.

That's it! Now all cells starting with A1 through the FIRST
one that exceeds the value in F1 are highlighted, but not the
later columns in that row.

 

[Guest]

I became confused also Karim. However, if Dallman's answer is basically
correct, then I suggest that you adjust the formula to only include the $
sign prefix on the columns and leave them off the rows otherwise you will
have to separately enter the conditional formatting formula for every row. If
you only make the column absolute and the row relative, then you can use:-

Copy-> Paste Special-> Formats to copy the formatting down to the following
rows.

Example: =AND(SUM($A1:$E1)>$F1,SUM($A1:$E1)-$E1<=$F1)

Regards,

OssieMac

 

[Dallman Ross]

I became confused also Karim. However, if Dallman's answer is
basically correct, then I suggest that you adjust the formula
to only include the $ sign prefix on the columns and leave them
off the rows otherwise you will have to separately enter the
conditional formatting formula for every row. If you only make
the column absolute and the row relative, then you can use:-

Good point.

Example: =AND(SUM($A1:$E1)>$F1,SUM($A1:$E1)-$E1<=$F1)

Except we can't have one on the E columns at the end!
My formula won't work in that case. I had:

=AND(SUM($A$1:$E$1)>$F$1,SUM($A$1:E$1)-E$1<=$F$1)
^ ^
| /
| /
| /
|/
*
No $!!!! That's important.

So the proposed change should be:

=AND(SUM($A1:$E1)>$F1,SUM($A1:E1)-E1<=$F1)


Also, it's important to have cell E1 be active when
you enter this formula. Otherwise, the relative references
will change and it won't work. Sorry I didn't make that clear.

You could enter it in E1 as a conditional format, then use
the Format Painter or the method Ossie described. Or you
could highlight the entire range while making sure that E1
appears in the "active" box left of the formula bar.
But if another cell is the active one when you enter it,
it won't work.

The same formula relative to A1, which is how I probably should
have presented it all along, is this:

=AND(SUM($A1:$E1)>$F1,SUM($A1:A1)-A1<=$F1)

I hope what I'm saying makes sense to you.

Copy-> Paste Special-> Formats to copy the formatting down to
the following rows.

Or, what I typically do is use the Format Painter and drag
across ranges I want to share that conditional formatting.

=dman=

 

[Guest]

Hi Guys,
Sorry about the confusion and poor explanation.
The formulas as suggested by Ossie or Dallman don't do what I am trying to do:
Columns A through E contain 5 days/week production levels, column F contain
the customer order. What I am trying to do is to examine production against
orders and higlight or shade (same thing) what will be used from production.
To put this into a numeric example:
A B C D E F
5 3 3 4 2 10

if the order is 10, then what will be used from production is 5 + 3 +3 (Then
A, B & C should be shaded or highlited). Since 5 + 3 + 3 = 11 and 11 is >
than 10, cell C should be higlighted although it will only be partially used.
In another scenario, if F = 8, A and B should only be shaded
or if F = 15, then A, B, C & D should be shaded.

Trying to generalize what I just explained using an if statement:
If F1 >0, shade A1 and continue evaluation, else stop
if F1>A1+0, shade B1 and continue evaluation, else stop
if F1>A1+B1, shade C1 and continue evaluation, else stop
if F1> A1+B1+C1, shade D1 and continue evaluation else stop
if F1> A1+B1+C1+D1, shade E1 else stop

The question in this case is how to formulate this programatically or using
conditional formating.

Sorry again about the confusion and I hope that this was a clear enough
explanation.
Thanks a lot for your help

Karim