Conditional formatting

[Eqbal]

Hi,

I need to format a row or column based on the value a cell in that row.
How could i do that? i.e. If the value of cell A1 is less than or equal to
0 u want the color pattern of B1 to H1 to change into green.

tnx in advance,
eqbal

 

[gatwickxx]

Usually conditional formatting is done based on the value of that cell.
In this case, use the drop down box to change from "Cell Value is" t
"Formula is". Select a cell and use the Toolbar Format - Conditiona
formatting. You need to write a formula with a logical (ie. TRUE o
FALSE) response. In this case, in row 1 the formula to trigger th
conditional format would be

=A1<=0

The format can then be set as you choose. the conditional format cn b
copied down and the formula is relative (as with formulae in cell) s
use $ signs as appropriate

 

[David McRitchie]

=A1<=0

The format can then be set as you choose. the conditional format can be
copied down and the formula is relative (as with formulae in cell) so
use $ signs as appropriate.

To color the entire row then ALL of your cell references
would have absolute column addressing. i.e. =$A1<= 0

There is no need to copy Conditional Formatting down, simply
select your column(s) or the entire worksheet (Ctr+A) before
entering your Conditional Formatting. You can use the Format
Painter, and you can use the fill-handle to fill down everything, but
it is best to set it up the way you want at the beginning.

More information on Conditional Formatting, on my site:
http://www.mvps.org/dmcritchie/excel/condfmt.htm

 

[Eqbal Vkilzadeh]

HI,

I used your comments so far as i understood them. But there is one problem
and that is i did not ask my question correctly:

actually i want to conditionally format my entire columns B to G according
to the value of their relative leftmost cell in column A in each row. i.e.
if the value of a1 is less than or equal to 0 then b1 to g1 becom blue or
...., and if the value of a2 is less than or equal to 0 b2 to g2 turn into
blue. if i use absolute references then all the conditions would refer to
a1, if i don't and select b1 to g1 and then use the conditional formatting
then the condition in c1 is automatically relative to b1 (that is if b1<=0
then ...). now please tell there is another way other than conditionally
formatting each cell from b1 to g1 relative to a1 and then using the fill
handle or format painter for the other rows below.

tnx,
eqbal

 

[David McRitchie]

You would use $A1 if the active cell is on Row 1 when you
enter your Conditional Formatting formulas.

I don't think you looked at my web page condfmt.htm

Select A1 so you are at the top of the worksheet.

Select Columns B:G since these are the only columns
you want to be colored. That means that B1 will be your
active cell. Since the formula is absolute to column ($A), it really
only matters that you are on row 1 when you enter the C.F.

Format, Conditional Formatting
condition 1:
formula is: =$A1 <= 0
format button, pattern, choose a light blue
=$A1 <= 0 B:G become blue

You are set up completely no C.F formula need to be
copied to any other cells, no fiddling with the Format Painter.

 

[Eqbal Vkilzadeh]

HI,

I used your comments so far as i understood them. But there is one problem
and that is i did not ask my question correctly:

actually i want to conditionally format my entire columns B to G according
to the value of their relative leftmost cell in column A in each row. i.e.
if the value of a1 is less than or equal to 0 then b1 to g1 becom blue or
...., and if the value of a2 is less than or equal to 0 b2 to g2 turn into
blue. if i use absolute references then all the conditions would refer to
a1, if i don't and select b1 to g1 and then use the conditional formatting
then the condition in c1 is automatically relative to b1 (that is if b1<=0
then ...). now please tell there is another way other than conditionally
formatting each cell from b1 to g1 relative to a1 and then using the fill
handle or format painter for the other rows below.

tnx,
eqbal

 

[Eqbal Vkilzadeh]

Now i get it. So there is a difference between $A and $A$. in your
previous mail i thought it was a typing mistake.

Thnaks a lot.

You would use $A1 if the active cell is on Row 1 when you
enter your Conditional Formatting formulas.

I don't think you looked at my web page condfmt.htm

Select A1 so you are at the top of the worksheet.

Select Columns B:G since these are the only columns
you want to be colored. That means that B1 will be your
active cell. Since the formula is absolute to column ($A), it really
only matters that you are on row 1 when you enter the C.F.

Format, Conditional Formatting
condition 1:
formula is: =$A1 <= 0
format button, pattern, choose a light blue
=$A1 <= 0 B:G become blue

You are set up completely no C.F formula need to be
copied to any other cells, no fiddling with the Format Painter.
---
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

HI,

I used your comments so far as i understood them. But there is one
problem
and that is i did not ask my question correctly:

actually i want to conditionally format my entire columns B to G
according
to the value of their relative leftmost cell in column A in each row.
i.e.
if the value of a1 is less than or equal to 0 then b1 to g1 becom blue
or
..., and if the value of a2 is less than or equal to 0 b2 to g2 turn
into
blue. if i use absolute references then all the conditions would refer
to
a1, if i don't and select b1 to g1 and then use the conditional
formatting
then the condition in c1 is automatically relative to b1 (that is if
b1<=0
then ...). now please tell there is another way other than conditionally
formatting each cell from b1 to g1 relative to a1 and then using the
fill
handle or format painter for the other rows below.

tnx,
eqbal

 

[David McRitchie]

I see you got your answer. Reposting simply caused your messages
to be refreshed. Glad it worked out and was understood.