Conditional Formatting based on entire column

M

mike

Ok for any given row, I want to turn colums B:M green if columns L:M
are not blank. In other words, when I enter something in any cell in
columns L:M, I want those cells as well as the cells to the left of
them to turn green (except A, but I could be flexible there if I had
to)

I've tried =NOT(ISBLANK($L:$M) on B:M and several other combinations
but none work. I'm thinking I'm having trouble finding a formula that
doesn't make Excel wait on the ENTIRE colums L:M be filled in. It
needs to trigger only if the columns in a particular row are filled
in.

Does this make sense the way I've said it?
 
B

Bernie Deitrick

Mike,

Select B2:M2, then use Format / CF.... Formula is with the formula

=AND($L2<>"",$M2<>"")

Select you formatting, OK, OK, and then copy B2:M2 and paste special formats as far down the sheet
as you need.

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Dave,

That formula would

1) need to be array entered to work (which CF cannot handle)
2) would require L and M need to be $L and $M

HTH,
Bernie
MS Excel MVP
 
B

Bob Phillips

It seems to me that you should select B:M for all desired rows, lets assume
you start at row 2, and use a CF formula of

=COUNTA($L2:$M2)<>0

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

mike

It seems to me that you should select B:M for all desired rows, lets assume
you start at row 2, and use a CF formula of

=COUNTA($L2:$M2)<>0

--
Click to expand...


Instead of blocking all the cells I wanted the format to work on, I
did it on one cell then copied down. Apparently I can't apply to all
cells at once; I must apply to one cell then copy so the formula
adjusts.

It's working now.

Thanks everyone.
 
B

Bob Phillips

That is not so Mike. As long as the row is relative, you can apply to a
block of rows, I do it all the time.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top