Conditional formatting

  • Thread starter Thread starter Eqbal
  • Start date Start date
E

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
 
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
 
=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.
Click to expand...

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
 
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
 
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.
 
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
 
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.
 
I see you got your answer. Reposting simply caused your messages
to be refreshed. Glad it worked out and was understood.
 
Back
Top