ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Condtional formatting (https://www.excelbanter.com/excel-worksheet-functions/22093-condtional-formatting.html)

68magnolia71

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

JE McGimpsey

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


68magnolia71

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




All times are GMT +1. The time now is 02:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com