Change 3rd cell colour based on cell 1 >= cell2 in range

On Dec 19, 3:20 pm, wombarrapete wrote:
> On Dec 19, 2:47 pm, "T. Valko" wrote:
>
>
>
> > Let's see if I have this straight....
>
> > 31 rows of data. 1 for each day of the month. Do you want to base this on
> > the current DAY of the month or the *last* entries which may or may not
> > correspond to the current day of the month depending on how often you update
> > the data.
>
> > I may be over analyzing this but that's what I tend to do!
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > "wombarrapete" wrote in message
>
>
>
> > > Hi all - I have looked through the group for a solution to this
> > > particular problem but i think my inexperience is possibly stopping me
> > > from seeing the solution .. so apologies if it has already been
> > > answered many times previously.
>
> > > My problem is this :
>
> > > I have two columns Col1 = Cumulative Sales and Col2 = Cumulative
> > > Target and a range of 1 - 31(signifies the days of the month).
>
> > > I have applied conditional formatting to all the cells in Col1 so that
> > > as a value is entered into a cell in Col1 the cell turns green if the
> > > value is >= to the corresponding cell in Col2, and red if < than the
> > > corresponding cell in Col2... this is working fine.
>
> > > This results in the cells in Col1 sometimes being green and sometimes
> > > being red depending on if we are under or over the target value each
> > > day of the month.
>
> > > However I also have a sum column at the bottom of Col1 which i want to
> > > mimic the colour of the current last filled in cell in Col1 - so that
> > > we always have a quick visual of red or green at the bottom of the
> > > spreadsheet as the month progresses.
>
> > > Hopefully I have explained myself clearly ... can someone point me in
> > > the right direction ?
>
> > > thanks
> > > pete
>
> Thanks Biff - the *last* entry ... i will be manually entering the
> data so you're right .. i may be tardy at times !!
>
> Also ... this needs to work for all months not just the ones with 31
> days ...
>
> cheers
> pete

Here's a bit more information .. and my latest attempt ... i hope the
formatting stays in or this will look like a mess !!

-----------------------
Col1 | Col2
-----------------------
440 | 500 <--- Col1 = red
-----------------------
1020 | 1000 <--- Col1 = green
-----------------------
1580 | 1500 <--- Col1 = green (and as it is the last
cell with a value in the Col1 column i want the SUM col
----------------------- at the bottom of
Col1 to also be green.
NA# | 2000
-----------------------
NA# | 2500
-----------------------
NA# | 3000

etc etc down to 31 ( or the last day of the month)

----------------------
| 15500
----------------------

SUM |
----------------------


Here is what I have at the moment in my conditional formatting for the
SUM column(the values are in Row B and Row C):

First turn cell green if >=

=INDEX(B2:B32,MATCH(9.99999999999999E+307,B2:B32)) >= INDEX
(C2:C32,MATCH(9.99999999999999E+307,C2:C32))

Or turn cell red if <

=INDEX(B2:B32,MATCH(9.99999999999999E+307,B2:B32)) < INDEX(C2:C32,MATCH
(9.99999999999999E+307,C2:C32))


This would work fine if the two columns had their last values in the
cells next to each other but my problem is that the values in COL2
continue on to the bottom as they let us know in advance what our
Cumulative Target is each day .. and that means that my SUM cell is
always red as it will always be less than the bottom cell in Col2
until near the end of the month.

Hope this helps to clarify the problem ... pete
 >> Stay informed about: Change 3rd cell colour based on cell 1 >= cell2 in range 

Let's see if I have this straight....

31 rows of data. 1 for each day of the month. Do you want to base this on
the current DAY of the month or the *last* entries which may or may not
correspond to the current day of the month depending on how often you update
the data.

I may be over analyzing this but that's what I tend to do!
 >> Stay informed about: Change 3rd cell colour based on cell 1 >= cell2 in range 

On Dec 19, 6:07 pm, "T. Valko" wrote:
> OK, I'm assuming that there will *always* be 2 entries per row like this:
>
> 250...777
>
> You'll *never* have something like this:
>
> 250...777
> 488........
>
> Let's assume your data is in the range A1:B31
>
> Select the cell you want to format
> Goto the menu Format>Conditional Formatting
> Condition 1
> Select the Formula Is option
> Enter this formula in the box on the right:
> =LOOKUP(1E+100,A1:A31)>=LOOKUP(1E+100,B1:B31)
> Click the Format button
> Select the Patterns tab
> Select a shade of GREEN
> OK
>
> Click the Add button
>
> Condition 2
> Select the Formula Is option
> Enter this formula in the box on the right:
> =LOOKUP(1E+100,A1:A31)wrote in message
>

> On Dec 19, 2:47 pm, "T. Valko" wrote:
>
>
>
> > Let's see if I have this straight....
>
> > 31 rows of data. 1 for each day of the month. Do you want to base this on
> > the current DAY of the month or the *last* entries which may or may not
> > correspond to the current day of the month depending on how often you
> > update
> > the data.
>
> > I may be over analyzing this but that's what I tend to do!
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > "wombarrapete" wrote in message
>
>
>
> > > Hi all - I have looked through the group for a solution to this
> > > particular problem but i think my inexperience is possibly stopping me
> > > from seeing the solution .. so apologies if it has already been
> > > answered many times previously.
>
> > > My problem is this :
>
> > > I have two columns Col1 = Cumulative Sales and Col2 = Cumulative
> > > Target and a range of 1 - 31(signifies the days of the month).
>
> > > I have applied conditional formatting to all the cells in Col1 so that
> > > as a value is entered into a cell in Col1 the cell turns green if the
> > > value is >= to the corresponding cell in Col2, and red if < than the
> > > corresponding cell in Col2... this is working fine.
>
> > > This results in the cells in Col1 sometimes being green and sometimes
> > > being red depending on if we are under or over the target value each
> > > day of the month.
>
> > > However I also have a sum column at the bottom of Col1 which i want to
> > > mimic the colour of the current last filled in cell in Col1 - so that
> > > we always have a quick visual of red or green at the bottom of the
> > > spreadsheet as the month progresses.
>
> > > Hopefully I have explained myself clearly ... can someone point me in
> > > the right direction ?
>
> > > thanks
> > > pete
>
> Thanks Biff - the *last* entry ... i will be manually entering the
> data so you're right .. i may be tardy at times !!
>
> Also ... this needs to work for all months not just the ones with 31
> days ...
>
> cheers
> pete

Biff thanks ... but that doesn't work for me ... I posted some more
information just before you offered your last help ..
I also posted my latest attemp which *almost* works ....
I will have rows that look like the following :

400...500
800...1000
1200...1500
NA#...2000
NA#...3000
NA#...3500
NA#...4000
NA#...4500

SUM
 >> Stay informed about: Change 3rd cell colour based on cell 1 >= cell2 in range 

OK, I'm assuming that there will *always* be 2 entries per row like this:

250...777

You'll *never* have something like this:

250...777
488........

Let's assume your data is in the range A1:B31

Select the cell you want to format
Goto the menu Format>Conditional Formatting
Condition 1
Select the Formula Is option
Enter this formula in the box on the right:
=LOOKUP(1E+100,A1:A31)>=LOOKUP(1E+100,B1:B31)
Click the Format button
Select the Patterns tab
Select a shade of GREEN
OK

Click the Add button

Condition 2
Select the Formula Is option
Enter this formula in the box on the right:
=LOOKUP(1E+100,A1:A31)<LOOKUP(1E+100,B1:B31)
Click the Format button
Select the Patterns tab
Select a shade of RED
OK out

Note that there will be a "momentary" time during data entry when the format
may be incorrect. When you enter the data I assume you enter it from left to
right. Once you enter the data in the left side cell and until you enter the
data in the right side cell the formulas will be evaluating the current row
to the previous row. You may not even notice this. However, if this is a
problem we can fix it but it adds a lot more complexity to the task.
 >> Stay informed about: Change 3rd cell colour based on cell 1 >= cell2 in range 

Try changing the formulas to:

=LOOKUP(1E+100,A1:A31)>=LOOKUP(1E+100,A1:A31,B1:B31)

=LOOKUP(1E+100,A1:A31)<LOOKUP(1E+100,A1:A31,B1:B31)
 >> Stay informed about: Change 3rd cell colour based on cell 1 >= cell2 in range 

On Dec 20, 5:32 am, "T. Valko" wrote:
> Try changing the formulas to:
>
> =LOOKUP(1E+100,A1:A31)>=LOOKUP(1E+100,A1:A31,B1:B31)
>
> =LOOKUP(1E+100,A1:A31)wrote in message
>

> On Dec 19, 6:07 pm, "T. Valko" wrote:
>
>
>
> > OK, I'm assuming that there will *always* be 2 entries per row like this:
>
> > 250...777
>
> > You'll *never* have something like this:
>
> > 250...777
> > 488........
>
> > Let's assume your data is in the range A1:B31
>
> > Select the cell you want to format
> > Goto the menu Format>Conditional Formatting
> > Condition 1
> > Select the Formula Is option
> > Enter this formula in the box on the right:
> > =LOOKUP(1E+100,A1:A31)>=LOOKUP(1E+100,B1:B31)
> > Click the Format button
> > Select the Patterns tab
> > Select a shade of GREEN
> > OK
>
> > Click the Add button
>
> > Condition 2
> > Select the Formula Is option
> > Enter this formula in the box on the right:
> > =LOOKUP(1E+100,A1:A31)wrote in message
>
>
> > On Dec 19, 2:47 pm, "T. Valko" wrote:
>
> > > Let's see if I have this straight....
>
> > > 31 rows of data. 1 for each day of the month. Do you want to base this
> > > on
> > > the current DAY of the month or the *last* entries which may or may not
> > > correspond to the current day of the month depending on how often you
> > > update
> > > the data.
>
> > > I may be over analyzing this but that's what I tend to do!
>
> > > --
> > > Biff
> > > Microsoft Excel MVP
>
> > > "wombarrapete" wrote in message
>
> >
>
> > > > Hi all - I have looked through the group for a solution to this
> > > > particular problem but i think my inexperience is possibly stopping me
> > > > from seeing the solution .. so apologies if it has already been
> > > > answered many times previously.
>
> > > > My problem is this :
>
> > > > I have two columns Col1 = Cumulative Sales and Col2 = Cumulative
> > > > Target and a range of 1 - 31(signifies the days of the month).
>
> > > > I have applied conditional formatting to all the cells in Col1 so that
> > > > as a value is entered into a cell in Col1 the cell turns green if the
> > > > value is >= to the corresponding cell in Col2, and red if < than the
> > > > corresponding cell in Col2... this is working fine.
>
> > > > This results in the cells in Col1 sometimes being green and sometimes
> > > > being red depending on if we are under or over the target value each
> > > > day of the month.
>
> > > > However I also have a sum column at the bottom of Col1 which i want to
> > > > mimic the colour of the current last filled in cell in Col1 - so that
> > > > we always have a quick visual of red or green at the bottom of the
> > > > spreadsheet as the month progresses.
>
> > > > Hopefully I have explained myself clearly ... can someone point me in
> > > > the right direction ?
>
> > > > thanks
> > > > pete
>
> > Thanks Biff - the *last* entry ... i will be manually entering the
> > data so you're right .. i may be tardy at times !!
>
> > Also ... this needs to work for all months not just the ones with 31
> > days ...
>
> > cheers
> > pete
>
> Biff thanks ... but that doesn't work for me ... I posted some more
> information just before you offered your last help ..
> I also posted my latest attemp which *almost* works ....
> I will have rows that look like the following :
>
> 400...500
> 800...1000
> 1200...1500
> NA#...2000
> NA#...3000
> NA#...3500
> NA#...4000
> NA#...4500
>
> SUM

Biff ... you're a legend !!

Thanks a million ... Merry Christmas to you and your loved ones ...

pete
 >> Stay informed about: Change 3rd cell colour based on cell 1 >= cell2 in range 

You're welcome. Thanks for the feedback!
 >> Stay informed about: Change 3rd cell colour based on cell 1 >= cell2 in range