Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Conditional Formatting based on entire column

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?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Conditional Formatting based on entire column

What happens if you use =NOT(ISBLANK(L1:M1)) ?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mike" wrote:

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?


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default Conditional Formatting based on entire column

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


"mike" wrote in message
oups.com...
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?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default Conditional Formatting based on entire column

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


"Dave F" wrote in message
...
What happens if you use =NOT(ISBLANK(L1:M1)) ?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mike" wrote:

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?




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default Conditional Formatting based on entire column

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)



"mike" wrote in message
oups.com...
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?





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Conditional Formatting based on entire column

Yes, you're right. Should have figured that one out!
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Bernie Deitrick" wrote:

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


"Dave F" wrote in message
...
What happens if you use =NOT(ISBLANK(L1:M1)) ?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mike" wrote:

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?





  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Conditional Formatting based on entire column

On Feb 6, 10:27 am, "Bob Phillips" wrote:
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



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.


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default Conditional Formatting based on entire column

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)



"mike" wrote in message
oups.com...
On Feb 6, 10:27 am, "Bob Phillips" wrote:
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



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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I use conditional formatting for an entire column? Patti F Excel Discussion (Misc queries) 21 February 26th 10 06:36 PM
conditional cell format based on cell in same row, previous column tamiluchi Excel Worksheet Functions 7 May 3rd 06 04:11 PM
Highlighting an entire row using conditional formatting. SystemHack Excel Worksheet Functions 5 January 27th 06 02:24 AM
column to column conditional formatting won't work, need formula rrupp Excel Worksheet Functions 1 August 23rd 05 10:06 PM
copying conditional formatting to an entire column MaryMalone Excel Discussion (Misc queries) 1 November 30th 04 05:04 AM


All times are GMT +1. The time now is 11:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"