format whole row..

[Guest]

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")

 

[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"

 

[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)

 

[Max]

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")

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

---

 

[Guest]

thanks..

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"

 

[Guest]

thanks..

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)

 

[Guest]

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

 

[Max]

Hi, that works great,..

Glad to hear that !

Is there a modifcation to that for where cursor is,
cond. format takes effect?

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


---

 

[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.