Conditional formatting of rows

  • Thread starter Thread starter Rolly
  • Start date Start date
R

Rolly

Hi there. I keep track of projects in an excel sheet. I'd like to forma
all the rows in the table for which the value in column G is th
abbreviation PUB. I'd like the other rows, for which the value i
column G is not PUB, to stay as they are. How can I do this
 
Rolly,

Select al the rows
Go to Conditional Formatting
Change Condition 1 to Is Formula
add a formula of =$G1="PUB"
Click Format, and format as required
OK
OK


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi
- select all the rows you want this format to be applied (assumption:
starting with row 1)
- Goto 'Format - Conditional Format'
- Enter the following formula: =$G1="PUB" -> change the '1' if your
selection start with a different row
- Choose a format as you like

HTH
Frank
 
Select A1:Z1000 or whatever makes sense. Do Format / Conditional Formatting,
change 'Cell Value' is to 'Formula is' and then put in the formula =$G1="PUB" -
Hit the format button, choose a light pastel pattern colour and hit OK.
 
Thanks very much, all. That works just great. So, how does that work? I
seems to me that would just format row one or cell G1. What does th
$mean
 
Rolly,

No Excel is smart. It applies the formula to all cells, adjusting the
formula as it works its way through the range.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Probably explained better in my
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Every cell that you select when you enter the C.F. will
effectively have the C.F. applied to it. The formula you enter is
relative to the active cell. The active cell would probably
be A1 but if $G is used any cell on the first row being
the active cell is fine.

The $G means that the column does not change
The 1 in $G1 refers to row 1 and the row will change as you go down.

The C.F. applied to cell A1 is checking the value of the
cell in column G because the $G is absolute.
So cell A1 is compared using $G1 in the formula, cell
B1 and all other cells on the first row as well. Cell A2
is compared using $G2 in the formula, as well as the
rest of the cells in row 2.

The advantage of using $G1 in the formula allows you to
select all columns (same as all rows), any single column,
or any combination of columns using the same formula.

Each cell has a limit of 3 C.F. applied to it. You could have
a lot of different C.F. on the worksheet, but each cell has
a limit of 3 that apply to it.

It's more efficient to apply the C.F. to the entire column than
to apply it to the first 100 rows, and it is a lot easier to find
the C.F. that applies to cells if you can select the same selection
as when you applied the C.F., but you can check an individual
cell for it's C.F.


Bob Phillips said:
Rolly,

No Excel is smart. It applies the formula to all cells, adjusting the
formula as it works its way through the range.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Rolly

The dollar sign in $G1 locks the column G reference but allows the 1 to
increment to 2, 3, 4 etc.

See help on "cell references" "absolute and relative"

Gord Dibben Excel MVP
 
Back
Top