Conditional Formatting with Relative Reference.

Conditional Formatting with Relative Reference.

Sample workbook:

A B C D E F


1. 4.5 6 7.4 8.3 10 2

2. 1 2 3 4 3 2

3. 1 2 3 4 5 6

4. 6 5 4 3 2 1



Hi,

How can Excel 2003 workbook be designed so that if a value of a cell is
smaller or greater than the value of the cell, then color of the cell is
auto formatted ?

Formatting of the cell is as follows: Green if value is greater and Red if
the value is smaller.

Desired result based on the above sample workbook:

A B C D E F

1. 4.5 Green Green Green Green Red

2. 1 Green Green Green Red Red

3. 1 Green Green Green Green Green

4. 6 Red Red Red Red Red


Peter

Select cell B1 & go to conditional formatting
use 'Cell value is' and 'less than' and then enter =$A1
use the Format button to shade the cell in Red
now add another test and repeat the steps above but use 'greater than'

When you're finished, copy the formatting from B1 to the other cells




"Swiatkowski Peter" wrote:

> Conditional Formatting with Relative Reference.
>
> Sample workbook:
>
> A B C D E F
>
>
> 1. 4.5 6 7.4 8.3 10 2
>
> 2. 1 2 3 4 3 2
>
> 3. 1 2 3 4 5 6
>
> 4. 6 5 4 3 2 1
>
>
>
> Hi,
>
> How can Excel 2003 workbook be designed so that if a value of a cell is
> smaller or greater than the value of the cell, then color of the cell is
> auto formatted ?
>
> Formatting of the cell is as follows: Green if value is greater and Red if
> the value is smaller.
>
> Desired result based on the above sample workbook:
>
> A B C D E F
>
> 1. 4.5 Green Green Green Green Red
>
> 2. 1 Green Green Green Red Red
>
> 3. 1 Green Green Green Green Green
>
> 4. 6 Red Red Red Red Red
>
>
> Peter
>
>
>

Your numbers are in A1:F4, select this range or select B1:F4 (it makes no
difference)
In Conditional formatting use "Cell value is", "greater than" , $A1 (not
$A$1) and format to red
Click Add
For second format use "Cell value is", "less than" , $A1 (not $A$1) and
format to green

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Swiatkowski Peter" <[email protected]> wrote in message
news:[email protected]...
> Conditional Formatting with Relative Reference.
>
> Sample workbook:
>
> A B C D E F
>
>
> 1. 4.5 6 7.4 8.3 10 2
>
> 2. 1 2 3 4 3 2
>
> 3. 1 2 3 4 5 6
>
> 4. 6 5 4 3 2 1
>
>
>
> Hi,
>
> How can Excel 2003 workbook be designed so that if a value of a cell is
> smaller or greater than the value of the cell, then color of the cell is
> auto formatted ?
>
> Formatting of the cell is as follows: Green if value is greater and Red if
> the value is smaller.
>
> Desired result based on the above sample workbook:
>
> A B C D E F
>
> 1. 4.5 Green Green Green Green Red
>
> 2. 1 Green Green Green Red Red
>
> 3. 1 Green Green Green Green Green
>
> 4. 6 Red Red Red Red Red
>
>
> Peter
>
>

Bonjour, *Swiatkowski Peter*

you said :
> A B C D E F
> 2. 1 2 3 4 3 2
> 2. 1 Green Green Green Red Red

3<1 ?
2<1?
maybe a mistake ?

Select B1:F4
1st condition :The formula is : =B1<$A1 format : red
2nd condition : The formula is : =B1 > $A1 format : green.

Care :
If the value of one cell in the selected range = the value of the column A,
what color ?
Green ? change =B1 > $A1 in =B1>=$A1
Red ? change =B1<$A1 in =B1<=$A1


--
Bien amicordialement,
P. *******

Hi Peter,
Are we supposed to read an extra pass to find out what
value you are checking to. Please make things simple to read.

To compare the cells in columns B:H to the cell in A

Select Cell B1 -- so that B1 will still be the active when you then
Select Columns B:H normally with B1 active but since $A1
is used actually anything on row 1 could be the active cell.

Format, Conditional formatting
Formula is (1): =B1>$A1 format as Green
Formula is (2): =B1<$A1 format as Red

I expect you want >= or else <=
but you gave nothing in your example for if values are equal.
Formula is (1): =AND(NOT(ISBLANK($A1)), B1>=$A1)
Formula is (2): =AND(NOT(ISBLANK($A1)), B1<$A1)

To read more about Conditional Formatting see:
http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Swiatkowski Peter" <[email protected]> wrote in message news:[email protected]...
> Conditional Formatting with Relative Reference.
>
> Sample workbook:
>
> A B C D E F
>
>
> 1. 4.5 6 7.4 8.3 10 2
>
> 2. 1 2 3 4 3 2
>
> 3. 1 2 3 4 5 6
>
> 4. 6 5 4 3 2 1
>
>
>
> Hi,
>
> How can Excel 2003 workbook be designed so that if a value of a cell is
> smaller or greater than the value of the cell, then color of the cell is
> auto formatted ?
>
> Formatting of the cell is as follows: Green if value is greater and Red if
> the value is smaller.
>
> Desired result based on the above sample workbook:
>
> A B C D E F
>
> 1. 4.5 Green Green Green Green Red
>
> 2. 1 Green Green Green Red Red
>
> 3. 1 Green Green Green Green Green
>
> 4. 6 Red Red Red Red Red
>
>
> Peter
>
>

Hi Peter,
Are we supposed to read an extra pass to find out what
value you are checking to. Please make things simple to read.

To compare the cells in columns B:H to the cell in A

Select Cell B1 -- so that B1 will still be the active when you then
Select Columns B:H normally with B1 active but since $A1
is used actually anything on row 1 could be the active cell.

Format, Conditional formatting
Formula is (1): =B1>$A1 format as Green
Formula is (2): =B1<$A1 format as Red

I expect you want >= or else <=
but you gave nothing in your example for if values are equal.
Formula is (1): =AND(NOT(ISBLANK($A1)), B1>=$A1)
Formula is (2): =AND(NOT(ISBLANK($A1)), B1<$A1)

To read more about Conditional Formatting see:
http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Swiatkowski Peter" <[email protected]> wrote in message news:[email protected]...
> Conditional Formatting with Relative Reference.
>
> Sample workbook:
>
> A B C D E F
>
>
> 1. 4.5 6 7.4 8.3 10 2
>
> 2. 1 2 3 4 3 2
>
> 3. 1 2 3 4 5 6
>
> 4. 6 5 4 3 2 1
>
>
>
> Hi,
>
> How can Excel 2003 workbook be designed so that if a value of a cell is
> smaller or greater than the value of the cell, then color of the cell is
> auto formatted ?
>
> Formatting of the cell is as follows: Green if value is greater and Red if
> the value is smaller.
>
> Desired result based on the above sample workbook:
>
> A B C D E F
>
> 1. 4.5 Green Green Green Green Red
>
> 2. 1 Green Green Green Red Red
>
> 3. 1 Green Green Green Green Green
>
> 4. 6 Red Red Red Red Red
>
>
> Peter
>
>