If formula for conditional formatting

[jevi]

Hi All,

I am trying to do a formula with if and but I can't achieve what I want. I need if the cell I6 is Tuesday and Wednesday and if cell K5 is equal to 1,2,3,6,8,13,19,22,25 and that the cell K6 is bigger than zero, in case it meets these requirements the cell should be formatted red.

Is a table with the rows date and day of the month and then on the columns Codes which some are 1,2,3,6,8,13,19,22,25 and then I have all the data in tha table.

=+IF(AND($I6="Tuesday";$I6="Thursday";K5=1;K5=2;K5=3;K5=6;K5=8;K5=13;K5=19;K5=22;K5=24;K5=25;K6>0);FALSE)

I can't find a way to make it work.
thnx

 

[GraH]

Book1
	H	I	J	K	L
1
2
3
4
5				1
6		Wednesday		1
7
8
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I6	Expression	=CF_Check	text	NO

=AND(OR(Sheet1!$I$6="Tuesday";Sheet1!$I$6="Wednesday");ISNUMBER(MATCH(Sheet1!$K$5;{1;2;3;6;8;13;19;22;25};0));Sheet1!$K$6>0)

 

[Joe4]

You need a combination of AND and OR statements.
Think about it, I6 cannot be both "Tuesday" and "Thursday" at the same time. It can only be one or the other (or neither).
So you check each cell with the AND part, but within each one, you use an OR to check for the different values that cell can be.

Also, you do not need to use IF in Conditional Formatting. AND and OR functions return boolean values (True/False) by default.

GraH posted a formula above that should do what you want.

 

[JoeMo]

Conditional formatting formulas must evaluate to either TRUE or FALSE. In your case (untested) maybe:
=AND(OR($I6="Tuesday",$I6= "Thursday"),$K$5=LOOKUP(K5,{1,2,3,6,8,13,19,22,25}))

Note: I use US regional settings - comma is the argument separator.

 

[GraH]

As a side note, array arguments do not work in CF, therefore I used a formula in the name manager.

 

[JoeMo]

As a side note, array arguments do not work in CF, therefore I used a formula in the name manager.

Thanks for that reminder - did not remember it! My CF formula in post #4 is no good.

 

[jevi]

Hi GraH,

I think your formula is great even thought maybe I don't know how to make it work in my file. I created a name manager CF_check but is not working but maybe I didn't explain my self as I should.

1. From the rows I6:I36 I have all the days of the months that are based on the date of H6:H36 first criteria should check all the days in this range who are Tuesday and Thursday.

2. From column K5:AN5 are all the codes that I have (from 0 to 40), and is the second criteria that should check that the code number should be {1;2;3;6;8;13;19;22;25}

3. From the range of data K6:AN36 I have different numbers in the cells and this is the third criteria that I need to color red the numbers that are >0, but that are only on the days of Tuesday and Thursday and that have the codes like 1 or 2 etc.

The idea is that the number of transaction in the days of Tuesday and Thursday should be 0, if you are code 1,2,3 etc, but sometimes there are >0 and I need to higlight these numbers.

I hope I explain better now...thank you so much for your help still.

 

[GraH]

Maybe remove $ from the row references in the formula.
You can also share your data via XL2BB. That way we may provide better solutions.

 

[VBasic2008]

You can use both formulas in a cell, but you can only use the second formulas with Conditional Formatting.

Comma

=AND(OR(I6={"Tuesday","Wednesday"}),OR(K5={1,2,3,6,8,13,19,22,25}),K6>0)

=AND(OR(I6="Tuesday",I6="Wednesday"),OR(K5=1,K5=2,K5=3,K5=6,K5=8,K5=13,K5=19,K5=22,K5=25),K6>0) Semi-colon =AND(OR(I6={"Tuesday";"Wednesday"});OR(K5={1;2;3;6;8;13;19;22;25});K6>0) =AND(OR(I6="Tuesday";I6="Wednesday");OR(K5=1;K5=2;K5=3;K5=6;K5=8;K5=13;K5=19;K5=22;K5=25);K6>0)

 

[jevi]

You can use both formulas in a cell, but you can only use the second formulas with Conditional Formatting.

Comma

=AND(OR(I6={"Tuesday","Wednesday"}),OR(K5={1,2,3,6,8,13,19,22,25}),K6>0)

=AND(OR(I6="Tuesday",I6="Wednesday"),OR(K5=1,K5=2,K5=3,K5=6,K5=8,K5=13,K5=19,K5=22,K5=25),K6>0) Semi-colon =AND(OR(I6={"Tuesday";"Wednesday"});OR(K5={1;2;3;6;8;13;19;22;25});K6>0) =AND(OR(I6="Tuesday";I6="Wednesday");OR(K5=1;K5=2;K5=3;K5=6;K5=8;K5=13;K5=19;K5=22;K5=25);K6>0)

VBasic...they borth work like a treat. My goal was the conditional formatting and it is what I needed for the sheet.

I thank you so much for your time in resolving my question. Thank you also to all the other members for their reply to my post.

I feel really happy that I got the solution. ))