Need help with conditional formatting.

[duskwood47]

Is it possible to create conditional formatting for a single cell based on what value is in the cell but also taking into consideration what day of the week it is.

For example - Mon through Thursday if cell value less than 85, then cell color red, if greater than 85 then cell color green, however if Friday, then if cell value is less than 104, cell color red, cell value greater than 104, cell value green?

 

[etaf]

you can use Weekday() for the day of the week , with an AND or OR

I assume Sat & Sun would be unformatted

What cell is the date in ?

What colour if the cell is equal to 85 or 104 ?

A blank cell is seen as zero , so if the value is blank should that be coloured

Something like

RED
Mon through Thursday if cell value less than 85, then cell color red,
if Friday, then if cell value is less than 104, cell color red,
OR( AND ( Weekday ( cell with date , 2) < 5 , Cell with value in <85 ) , AND(Weekday ( cell with date , 2) = 5 , Cell with value in < 104) )

GREEN
if greater than 85 then cell color green,
cell value greater than 104, cell value green?

OR( AND ( Weekday ( cell with date , 2) < 5 , Cell with value in > 85 ) , AND(Weekday ( cell with date , 2) = 5 , Cell with value in > 104) )

example data set.xlsx
	A	B	C	D	E
1	Weekday	Value		RED	GREEN
2	1/1/21		Friday	TRUE	FALSE
3	1/2/21		Saturday	FALSE	FALSE
4	1/3/21		Sunday	FALSE	FALSE
5	1/4/21	50	Monday	TRUE	FALSE
6	1/5/21	100	Tuesday	FALSE	TRUE
7	1/6/21	40	Wednesday	TRUE	FALSE
8	1/7/21	33	Thursday	TRUE	FALSE
9	1/8/21	100	Friday	TRUE	FALSE
10	1/9/21		Saturday	FALSE	FALSE
11	1/10/21		Sunday	FALSE	FALSE
12	1/11/21	50	Monday	TRUE	FALSE
13	1/12/21	100	Tuesday	FALSE	TRUE
14	1/13/21	40	Wednesday	TRUE	FALSE
15	1/14/21	33	Thursday	TRUE	FALSE
16	1/15/21	100	Friday	TRUE	FALSE
17	1/16/21	50	Saturday	FALSE	FALSE
18	1/17/21	100	Sunday	FALSE	FALSE
19	1/18/21	40	Monday	TRUE	FALSE
20	1/19/21	33	Tuesday	TRUE	FALSE
21	1/20/21	100	Wednesday	FALSE	TRUE
22	1/21/21		Thursday	TRUE	FALSE
23	1/22/21		Friday	TRUE	FALSE
Sheet2

Cell Formulas
Range	Formula
C2:C23	C2	=A2
D2:D23	D2	=OR(AND(WEEKDAY(A2,2)<5,B2<85),AND(WEEKDAY(A2,2)=5,B2<104))
E2:E23	E2	=OR(AND(WEEKDAY(A2,2)<5,B2>85),AND(WEEKDAY(A2,2)=5,B2>104))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B23	Expression	=OR(AND(WEEKDAY(A2,2)<5,B2>85),AND(WEEKDAY(A2,2)=5,B2>104))	text	NO
B2:B23	Expression	=OR(AND(WEEKDAY(A2,2)<5,B2<85),AND(WEEKDAY(A2,2)=5,B2<104))	text	NO

 

[duskwood47]

you can use Weekday() for the day of the week , with an AND or OR

I assume Sat & Sun would be unformatted

What cell is the date in ?

What colour if the cell is equal to 85 or 104 ?

Something like

RED
Mon through Thursday if cell value less than 85, then cell color red,
if Friday, then if cell value is less than 104, cell color red,
OR( AND ( Weekday ( cell with date , 2) < 5 , Cell with value in <85 ) , AND(Weekday ( cell with date , 2) = 5 , Cell with value in < 104) )

GREEN
if greater than 85 then cell color green,
cell value greater than 104, cell value green?

OR( AND ( Weekday ( cell with date , 2) < 5 , Cell with value in > 85 ) , AND(Weekday ( cell with date , 2) = 5 , Cell with value in > 104) )

I have an =today() in cell G1.

The cell requiring the color formatting is in cell B2.

if the cell is equal to 85 or 104 then still red.

Correct, Sat and Sun would be unformatted

 

[etaf]

so are you just just colouring the cells based on today's day of week ?
did you want to use G1 or just include today() in the conditional formatting formula

So this is with today() in formula

OR you can change today() to use $G$1

GREEN
=OR(AND(WEEKDAY(TODAY(),2)<5,B2>85),AND(WEEKDAY(TODAY(),2)=5,B2>104))

RED
=OR(AND(WEEKDAY(TODAY(),2)<5,B2<=85),AND(WEEKDAY(TODAY(),2)=5,B2<=104))

example data set.xlsx
	B
2	104
3	105
4	85
5	50
6	100
7	40
8	33
9	200
10
11
12	50
13	400
14	40
15	33
16	100
17	500
18	100
19	40
20	33
21	100
22
23
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B23	Expression	=OR(AND(WEEKDAY(TODAY(),2)<5,B2>85),AND(WEEKDAY(TODAY(),2)=5,B2>104))	text	NO
B2:B23	Expression	=OR(AND(WEEKDAY(TODAY(),2)<5,B2<=85),AND(WEEKDAY(TODAY(),2)=5,B2<=104))	text	NO

 

[duskwood47]

so are you just just colouring the cells based on today's day of week ?
did you want to use G1 or just include today() in the conditional formatting formula

So this is with today() in formula

OR you can change today() to use $G$1

GREEN
=OR(AND(WEEKDAY(TODAY(),2)<5,B2>85),AND(WEEKDAY(TODAY(),2)=5,B2>104))

RED
=OR(AND(WEEKDAY(TODAY(),2)<5,B2<=85),AND(WEEKDAY(TODAY(),2)=5,B2<=104))

example data set.xlsx
	B
2	104
3	105
4	85
5	50
6	100
7	40
8	33
9	200
10
11
12	50
13	400
14	40
15	33
16	100
17	500
18	100
19	40
20	33
21	100
22
23
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B23	Expression	=OR(AND(WEEKDAY(TODAY(),2)<5,B2>85),AND(WEEKDAY(TODAY(),2)=5,B2>104))	text	NO
B2:B23	Expression	=OR(AND(WEEKDAY(TODAY(),2)<5,B2<=85),AND(WEEKDAY(TODAY(),2)=5,B2<=104))	text	NO

No. I am changing it based on the value in the cell which is dynamically changing based on a vba counter i built. However the requirement for our "goal" depends on what day it is. So Monday through thursday, our goal is 85, friday its 103.

 

[etaf]

our goal is 85, friday its 103.

on Mon-Thur - Red = 85
on Fri - Red = 104

if you want to use G1 then

GREEN
=OR(AND(WEEKDAY($G$1,2)<5,B2>85),AND(WEEKDAY($G$1,2)=5,B2>104))

RED
=OR(AND(WEEKDAY($G$1,2)<5,B2<=85),AND(WEEKDAY($G$1,2)=5,B2<=104))

 

[duskwood47]

Thanks for the help. Looks like it worked. Appreciate it. Ill know for sure when I hit 104 for the day today

 

[etaf]

ok, i'm in uk, so signing off soon
you are welcome