Conditional Formatting based on entire column

  • Thread starter Thread starter mike
  • Start date Start date
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?
 
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
 
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
 
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)
 
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.
 
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)
 
Back
Top