Conditional Fomatting - IF statements for string

[Dave_P_C]

Hello,

I use Excel 2007

In a spreadsheet I want to be able to set different conditional formatting based on 2 situations.

a) If the string "present" is in cells B2 to I2 format the row pale green
b) If the string "P" is in cell J2 format the row dark green

I have tried to achieve this by using conditional formatting as follows: -

a) =IF($B2:$I2="present",TRUE,FALSE) ---> Format pale green
b) =IF($J1="P",TRUE,FALSE) --> Format dark greeen

The problems I have are that if the string "present" is in any cell after column B the row is not getting formatted in pale green and if the string "P" is in column J the row is not getting formatted dark green (unless the value "present" is in either not on that row or in a column after B.

Conditional formatting b) should superseed conditional formatting a)

Please note - in the actual spreadsheet the string "present" in clolumns B to I is determined from a formula looking up a value on another sheet..

A very cut-down example of how the spreadsheet

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	System	B01	B02	B03	B04	B05	B06	B07	B08	Setup
2	A11	present	not present	not present	not present	not present	not present	not present	not present	P
3	A12	not present	not present	not present	not present	not present	not present	not present	not present
4	A13	not present	not present	not present	not present	not present	not present	not present	not present
5	A14	not present	not present	present	present	not present	not present	not present	not present	P
6	A15	not present	not present	not present	not present	not present	not present	not present	not present
7	A16	not present	not present	not present	present	not present	not present	not present	not present
Sheet1

Excel 2007



Can anyone advise how to fix this issue ?

Thank you.

 

[GlennUK]

If condition b) is to supercede condition a), then untick the Stop If True in condition a), or move condition b) to be before condition a).

 

[jim may]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	System	B01	B02	B03	B04	B05	B06	B07	B08	Setup	Just present?
2	A11	present	not present	not present	not present	not present	not present	not present	not present	P	TRUE
3	A12	not present	not present	not present	not present	not present	not present	not present	not present		FALSE
4	A13	not present	not present	not present	not present	not present	not present	not present	not present		FALSE
5	A14	not present	not present	present	present	not present	not present	not present	not present	P	TRUE
6	A15	not present	not present	not present	not present	not present	not present	not present	not present		FALSE
7	A16	not present	not present	not present	present	not present	not present	not present	not present		TRUE
8
Sheet1

Excel 2007

Cell Formulas
Range	Formula
K2	=COUNTIF(B2:I2,"present")>0

 

[jasonb75]

Try

a) =$J1="P"
b) =COUNTIF($B2:$I2,"present")

 

[Dave_P_C]

Hi all,

Thank you for your replies.

GlennUK - I did not have Stop If True selected but the conditions were still not applying correctly.

jim may / jason_b75 - I have used the formula =COUNTIF($B2:$I2,"present") and got it to work.

Part of the issue I had was that I had "Format as table" selected on the sheet and this seemed to superseed anything else.

Having disabled that and used the formulas provided it now works.

Thank you very much.