format whole row..

N

nastech

Hi, looking for way to cond. format whole row for 3 dif conditions left = 1 2
or 3.

trying to find something bit dynamic like: (not quite working, thanks)
=OR(LEFT(CELL(),1)="1")
 
R

Ron de Bruin

Select the whole row (click on the row header)

When you do this for row 1 A1 is active

Use this formula for the first condition
=LEFT(A1,1)="1"
 
B

Bob Phillips

Something like

=OR(LEFT($A1,1)="1",LEFT($A1,1)="2",LEFT($A1,1)="3")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Max

nastech said:
looking for way to cond. format
whole row for 3 dif conditions left = 1 2 or 3.
trying to find something bit dynamic like:
(not quite working, thanks)
=OR(LEFT(CELL(),1)="1")
Click to expand...

Assuming we have text data within col A, eg:

1A
2B
3C
1E
1K
3K
etc

then we could select the entire sheet (with A1 active)
and use something like this
as the CF conds 1 - 3's "Formula is"

Cond1: =LEFT($A1,1)+0=1
Cond2: =LEFT($A1,1)+0=2
Cond3: =LEFT($A1,1)+0=3

The CF would color the entire row*
based on the leftmost digit in col A
*the dollar sign in "$A.." is the key for entire row formatting
 
N

nastech

thanks..

Ron de Bruin said:
Select the whole row (click on the row header)

When you do this for row 1 A1 is active

Use this formula for the first condition
=LEFT(A1,1)="1"
Click to expand...
 
N

nastech

thanks..

Bob Phillips said:
Something like

=OR(LEFT($A1,1)="1",LEFT($A1,1)="2",LEFT($A1,1)="3")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
N

nastech

Hi, that works great,.. Is there a modifcation to that for where cursor is,
cond. format takes effect? thanks..
 
M

Max

nastech said:
Hi, that works great,..
Click to expand...

Glad to hear that !
Is there a modifcation to that for where cursor is,
cond. format takes effect?
Click to expand...

Ah, think it's not possible via normal CF <g>

Not sure about vba though,
but that route's beyond my depth to offer

Hang around awhile to see whether Ron / Bob, or
others versed in vba might jump in with something for you

In the interim, perhaps a possible "fuzzy" fit <g>
as to the cursor sensitive mod you're after
might be Chip's RowLiner:
http://www.cpearson.com/excel/RowLiner.htm
 
D

David McRitchie

Maybe I don't understand your question, as it seems it was in Max's
reply, perhaps this is a bit more explicit..

The formula used is based on the active cell location when the formula
is entered. The formula with adjustments relative to that active cell
is applied to all cells in the selection. The $A means that for each
cell on the row the column A cell will be the cell tested. See
http://www.mvps.org/dmcritchie/excel/condfmt.htm

So if you used A1 it would be testing it's own cell if A1 were the active
cell with $A1 being test your cursor could be anywhere on Row 1 to
get the same result for that address in the formula.
 
Top