If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Condtional formatting
Hello everyone,
I'm looking for a condtional formatting formula based on 2 conditions. The conditions are based on a text that are in 2 different cells in the same row. For example: if A1 contains "Start" and A2 contains "Operation" I want row 1 green; if if A1 contains "Stop" and A2 contains "Operation" I want row 1 red. A1 and A2 contain drop down list. All other combinations should give the rows blue. This last condition is a normal cell format. At least 15 columns in the speadsheet. Has someone an idea solve the problem? Thank you for any suggestion |
#2
|
|||
|
|||
One way:
Select Row 1 and enter CF1: Formula is =AND($A1="Start",$A2="Operation") Format1: Green CF2: Formula is =AND($A1="Stop",$A2="Operation") Format2: Red CF3: Formula is =COUNTA($A1:$A2)=2 Format3: Blue If the last condition is really a normal cell format, disregard it and format it directly as blue. Note that you said you conditions were "based on a text that are in 2 different cells in the same row". However, your examples were for 2 cells in the same column and different rows. The above formulae are consistent with your examples. In article , 68magnolia71 wrote: I'm looking for a condtional formatting formula based on 2 conditions. The conditions are based on a text that are in 2 different cells in the same row. For example: if A1 contains "Start" and A2 contains "Operation" I want row 1 green; if if A1 contains "Stop" and A2 contains "Operation" I want row 1 red. A1 and A2 contain drop down list. All other combinations should give the rows blue. This last condition is a normal cell format. At least 15 columns in the speadsheet. Has someone an idea solve the problem? Thank you for any suggestion |
#3
|
|||
|
|||
Hello afain JE,
Thiese formulas also worked fine, I used the third possibility for another combination of 3 two words. I dare submit another problem: In A1 I have a text with concatenated parts with at the left the date and at right the shift. "15 April 05 Shift M " for example. The date need be text for concatanation. I want the format changed one day in the week. I wrote following conditional formatting: =weekday(left($A1;8))=4 -- Format Blue. Copied it down through row 93 ( 31 times 3).It didn't work. =weekday(today())=4 -- Format Blue would work. Where is the problem? Thank you. 68magnolia71 "JE McGimpsey" wrote: One way: Select Row 1 and enter CF1: Formula is =AND($A1="Start",$A2="Operation") Format1: Green CF2: Formula is =AND($A1="Stop",$A2="Operation") Format2: Red CF3: Formula is =COUNTA($A1:$A2)=2 Format3: Blue If the last condition is really a normal cell format, disregard it and format it directly as blue. Note that you said you conditions were "based on a text that are in 2 different cells in the same row". However, your examples were for 2 cells in the same column and different rows. The above formulae are consistent with your examples. In article , 68magnolia71 wrote: I'm looking for a condtional formatting formula based on 2 conditions. The conditions are based on a text that are in 2 different cells in the same row. For example: if A1 contains "Start" and A2 contains "Operation" I want row 1 green; if if A1 contains "Stop" and A2 contains "Operation" I want row 1 red. A1 and A2 contain drop down list. All other combinations should give the rows blue. This last condition is a normal cell format. At least 15 columns in the speadsheet. Has someone an idea solve the problem? Thank you for any suggestion |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
difficulty with conditional formatting | Deb | General Discussion | 0 | March 23rd, 2005 06:13 PM |
conditional formatting question | Deb | General Discussion | 0 | March 23rd, 2005 02:07 AM |
Required Field Conditional | Katherine R | New Users | 2 | September 1st, 2004 05:52 AM |
Problems with Style formatting | terrapinie | Formatting Long Documents | 2 | July 7th, 2004 06:18 PM |