Conditional Formatting last n rows as data entered

[nburaq]

Hi,

I have data set as shown below and this is a dynamic data based on the last 12 months. I would like to highlight the cells of last 12 months if value is greater than 20. So, based on this example, between april 23- mar 24 only values of Apr/23,Jun/23, Sep/23 and Mar/24 will be highlighted as green and if I enter value for Apr/24 then THE new range will be May/23-Apr/24 and cells will be highlighted with new range.

Location

Floor

Work

Jan/23

Feb/23

Mar/23

Apr/23

May/23

Jun/23

Jul/23

Aug/23

Sep/23

Oct/23

Nov/23

Dec/23

Jan/24

Feb/24

Mar/24

Apr/24

May/24

Jun/24

Jul/24

Aug/24

Sep/24

Oct/24

Nov/24

Dec/24

North

1st

PM

20,23

18,42

15,78

25,78

18,94

36,04

16,87

17,36

26,30

17,44

17,31

15,64

16,46

15,32

27,41

Thanks for the comments and your help.

 

[SanjayGMusafir]

It can be done. But it all depends on how you are entering Month-Year values in cells.

If they are dates then you can Conditional Format them. If they are manual text(s) better change them at earliest. Sooner or later you will need them top be dates to work with.

 

[SanjayGMusafir]

It can be done. But it all depends on how you are entering Month-Year values in cells.

If they are dates then you can Conditional Format them. If they are manual text(s) better change them at earliest. Sooner or later you will need them top be dates to work with.

Check this and revert -

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024	Mar-2024	Apr-2024
2
Sheet1

Cell Formulas
Range	Formula
B1:Q1	B1	=DATE(2023,SEQUENCE(1,16,1,1),1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
1:1	Expression	=AND(A1<=MAX(1:1),A1>=EDATE(MAX(1:1),-11))	text	NO

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Location	Floor	Work	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
2	North	1st	PM	20.23	18.42	15.78	25.78	18.94	36.04	16.87	17.36	26.3	17.44	17.31	15.64	16.46	15.32	27.41
3				20.23	18.42	15.78	25.78	18.94	36.04	16.87	17.36	26.3	17.44	17.31	15.64	16.46	15.32	27.41	19.5	21.2
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:AA4	Expression	=AND(D2>20,COUNTIFS(D2:$AA2,"<>")<=12)	text	NO

 

[SanjayGMusafir]

Just realized another condition was values above 20

 

[nburaq]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Location	Floor	Work	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
2	North	1st	PM	20.23	18.42	15.78	25.78	18.94	36.04	16.87	17.36	26.3	17.44	17.31	15.64	16.46	15.32	27.41
3				20.23	18.42	15.78	25.78	18.94	36.04	16.87	17.36	26.3	17.44	17.31	15.64	16.46	15.32	27.41	19.5	21.2
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:AA4	Expression	=AND(D2>20,COUNTIFS(D2:$AA2,"<>")<=12)	text	NO

Thanks a lot it works perfectly! and thanks all to you!

 

[Fluff]

Glad we could help & thanks for the feedback.