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

[wombarrapete]

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

 

[T. Valko]

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!

 

[wombarrapete]

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!

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

 

[wombarrapete]

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

 

[T. Valko]

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.

--
Biff
Microsoft Excel MVP

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!

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

 

[wombarrapete]

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

--
Biff
Microsoft Excel MVP






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

 

[wombarrapete]

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)

--
Biff
Microsoft Excel MVP












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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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)

--
Biff
Microsoft Excel MVP












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