Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use conditional formatting for an entire column? | Excel Discussion (Misc queries) | |||
conditional cell format based on cell in same row, previous column | Excel Worksheet Functions | |||
Highlighting an entire row using conditional formatting. | Excel Worksheet Functions | |||
column to column conditional formatting won't work, need formula | Excel Worksheet Functions | |||
copying conditional formatting to an entire column | Excel Discussion (Misc queries) |