How to highlight cell with value?

B

Brett

I'd like to bold any cell with a value greater than 0. The value is
changed manually and will sometimes be changed manually back to 0.
How is this done?

Thanks,
Brett
 
K

Ken Russell

Use Conditional Formatting, under Format. Then set the condition as Greater
Than 0 and select Format Bold. Use the Format Paint brush to copy the
format to the required cells.

Ken Russell
Remove hat to reply by e-mail
[email protected]

| I'd like to bold any cell with a value greater than 0. The value is
| changed manually and will sometimes be changed manually back to 0.
| How is this done?
|
| Thanks,
| Brett
 
B

Brett

Perfect. How can I have that entire row highlight? Some of the other
cells on that row are currency, text and number values. If the one
particular cell's value is greater than 0, I want the entire row to
bold or at least the ones I'm using on that row. I need a way to tell
the other cells to look at the one cell rather than themselves, which
is all the conditional format will let me do.

Thanks,
Brett
 
D

David McRitchie

Hi Brett,
Changing the column letter to an absolute with the $ sign
makes the other cells on that row low to that cell as you=
asked for.

Which cells can be get coloring is based on the selection.

The formula itself is based on the active cell. Each cell in
the selection will be tested and uses 1 of the 3 conditions
per cell that you are allowed in C.F.

Therefore, you would make the column portions absolute
in your C.F. formula, when you want to color by rows.

Supose you want to conditionally format cell D2
C.F. Formula 1: =D2>0 with select cell D2 selected

Suppose you want to Conditional format all of row 2 if D2 > 0
Select the entire row 2
C.F. Formula 1: =$D2>0

Suppose you want to Conditionally format all rows where
Column D is greater than 0
Select Cell D1 to be the active cell
Select All Cells Ctrl+A, with D1 still the active cell
acually any cell on row 1 can be the active cell
C.F. Formula 1: =$D1>0
If you may have text in column D then change the formula to
C.F. Formula 1: =AND($D1>0,ISNUMBER($D1))


The cells to be colored are based on the selection
All cells in the selection are based on the C.F.
so all cells on row1 are checking cell $D1
all cells on row2 are checking cell $D2
etc. etc.

and all cells in the worksheet have used up of of
the 3 C.F. conditions allowed.

For more information on Condiditonal Formatting se
http://www.mvps.org/dmcritchie/excel/condfmt.htm
 
B

Brett

Great! I have that working for the one row. Problem is there are
over 100 rows I need this to work for. Is there a quick way to
copy/paste this formula so it will work with each row?

Thanks,
Brett

David McRitchie said:
Hi Brett,
Changing the column letter to an absolute with the $ sign
makes the other cells on that row low to that cell as you=
asked for.

Which cells can be get coloring is based on the selection.

The formula itself is based on the active cell. Each cell in
the selection will be tested and uses 1 of the 3 conditions
per cell that you are allowed in C.F.

Therefore, you would make the column portions absolute
in your C.F. formula, when you want to color by rows.

Supose you want to conditionally format cell D2
C.F. Formula 1: =D2>0 with select cell D2 selected

Suppose you want to Conditional format all of row 2 if D2 > 0
Select the entire row 2
C.F. Formula 1: =$D2>0

Suppose you want to Conditionally format all rows where
Column D is greater than 0
Select Cell D1 to be the active cell
Select All Cells Ctrl+A, with D1 still the active cell
acually any cell on row 1 can be the active cell
C.F. Formula 1: =$D1>0
If you may have text in column D then change the formula to
C.F. Formula 1: =AND($D1>0,ISNUMBER($D1))


The cells to be colored are based on the selection
All cells in the selection are based on the C.F.
so all cells on row1 are checking cell $D1
all cells on row2 are checking cell $D2
etc. etc.

and all cells in the worksheet have used up of of
the 3 C.F. conditions allowed.

For more information on Condiditonal Formatting se
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

Brett said:
Perfect. How can I have that entire row highlight? Some of the other
cells on that row are currency, text and number values. If the one
particular cell's value is greater than 0, I want the entire row to
bold or at least the ones I'm using on that row. I need a way to tell
the other cells to look at the one cell rather than themselves, which
is all the conditional format will let me do.
Click to expand...
Click to expand...
 
D

Dave Peterson

Can you copy all the formats (including conditional formatting)?

If yes, then how about copy|paste special|formats?

If no, then you could select all the range that should get this conditional
formatting. Then Format|conditional formatting. But write the formula with
respect to the active cell.

Excel will ajust the formula for each cell when you click ok.

You will have to be careful with your relative/absolute addressing, though.

$d$2 won't adjust anywhere
$d2 will adjust for other rows.


Great! I have that working for the one row. Problem is there are
over 100 rows I need this to work for. Is there a quick way to
copy/paste this formula so it will work with each row?

Thanks,
Brett

David McRitchie said:
Hi Brett,
Changing the column letter to an absolute with the $ sign
makes the other cells on that row low to that cell as you=
asked for.

Which cells can be get coloring is based on the selection.

The formula itself is based on the active cell. Each cell in
the selection will be tested and uses 1 of the 3 conditions
per cell that you are allowed in C.F.

Therefore, you would make the column portions absolute
in your C.F. formula, when you want to color by rows.

Supose you want to conditionally format cell D2
C.F. Formula 1: =D2>0 with select cell D2 selected

Suppose you want to Conditional format all of row 2 if D2 > 0
Select the entire row 2
C.F. Formula 1: =$D2>0

Suppose you want to Conditionally format all rows where
Column D is greater than 0
Select Cell D1 to be the active cell
Select All Cells Ctrl+A, with D1 still the active cell
acually any cell on row 1 can be the active cell
C.F. Formula 1: =$D1>0
If you may have text in column D then change the formula to
C.F. Formula 1: =AND($D1>0,ISNUMBER($D1))


The cells to be colored are based on the selection
All cells in the selection are based on the C.F.
so all cells on row1 are checking cell $D1
all cells on row2 are checking cell $D2
etc. etc.

and all cells in the worksheet have used up of of
the 3 C.F. conditions allowed.

For more information on Condiditonal Formatting se
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

Brett said:
Perfect. How can I have that entire row highlight? Some of the other
cells on that row are currency, text and number values. If the one
particular cell's value is greater than 0, I want the entire row to
bold or at least the ones I'm using on that row. I need a way to tell
the other cells to look at the one cell rather than themselves, which
is all the conditional format will let me do.
Click to expand...
Click to expand...
Click to expand...
 
B

Brett

Dave Peterson said:
Can you copy all the formats (including conditional formatting)?
Click to expand...
This is the question I'm asking you.
If yes, then how about copy|paste special|formats?
Click to expand...
I tried this but only the one cell will bold.
If no, then you could select all the range that should get this conditional
formatting. Then Format|conditional formatting. But write the formula with
respect to the active cell.

Excel will ajust the formula for each cell when you click ok.

You will have to be careful with your relative/absolute addressing, though.

$d$2 won't adjust anywhere
Click to expand...
$d2 will adjust for other rows.
Click to expand...
Worked perfect. Thanks.
Great! I have that working for the one row. Problem is there are
over 100 rows I need this to work for. Is there a quick way to
copy/paste this formula so it will work with each row?

Thanks,
Brett

David McRitchie said:
Hi Brett,
Changing the column letter to an absolute with the $ sign
makes the other cells on that row low to that cell as you=
asked for.

Which cells can be get coloring is based on the selection.

The formula itself is based on the active cell. Each cell in
the selection will be tested and uses 1 of the 3 conditions
per cell that you are allowed in C.F.

Therefore, you would make the column portions absolute
in your C.F. formula, when you want to color by rows.

Supose you want to conditionally format cell D2
C.F. Formula 1: =D2>0 with select cell D2 selected

Suppose you want to Conditional format all of row 2 if D2 > 0
Select the entire row 2
C.F. Formula 1: =$D2>0

Suppose you want to Conditionally format all rows where
Column D is greater than 0
Select Cell D1 to be the active cell
Select All Cells Ctrl+A, with D1 still the active cell
acually any cell on row 1 can be the active cell
C.F. Formula 1: =$D1>0
If you may have text in column D then change the formula to
C.F. Formula 1: =AND($D1>0,ISNUMBER($D1))


The cells to be colored are based on the selection
All cells in the selection are based on the C.F.
so all cells on row1 are checking cell $D1
all cells on row2 are checking cell $D2
etc. etc.

and all cells in the worksheet have used up of of
the 3 C.F. conditions allowed.

For more information on Condiditonal Formatting se
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

Perfect. How can I have that entire row highlight? Some of the other
cells on that row are currency, text and number values. If the one
particular cell's value is greater than 0, I want the entire row to
bold or at least the ones I'm using on that row. I need a way to tell
the other cells to look at the one cell rather than themselves, which
is all the conditional format will let me do.
Click to expand...
Click to expand...
Click to expand...
 
Top