VBA - highlight consecutive

[mahmed1]

Hi, I am really hoping someone can help me with some VBA code that can help me with this as ive had no luck with a previous thread

I have a row of data

Say B6 (I have % target variable) ie >70%
In D6 (I have consecutive day target 3)
In E6 (to last column as data will get added in column and each day) i have the data

What i want the VBA code to do is if the value from E6 to last column is
More than target % and has remained like that for more than or = to consecutive day target then highlight that value red colour...

Eg if the values are
69, 67,68,71,70,71 (highlight 71), 77, (highlight 77), 55,67,78,78,60,61,70,71,77 (highlight 77), 78 (highlight 78), 75 (highlight 75)

I hope this makes sense and really hope someone can help me

The other thing i have is in row E3 i have the dates listed..... I want to have a macro that filters by days
So say i had a macro to only display mondays data (i can loop through to hide other columns and just show mondays columns) and then what if want the highlight code to is still check for the condition but look at the visible cells

So only look at the visible columns from E3 and look at the condition and highlight

Please can someone help me with this

Thank You

 

[mahmed1]

Bump
Any help would be appreciated

 

[Peter_SSs]

Not sure about the second part but for the first part, does this Conditional Formatting do what you want?

20 10 20.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
6		70		3	69	67	68	71	70	71	77	55	67	78	78	60	61	70	71	77	78	75
7		50		7	80	70	60	78	78	90	80	20	50	50	50	50	50	50	50	50	60	65	62	70	10	15
8		48		2	50	48	47	40	54	59	39	61	64	34	68	50	58	60	50	31	60	54	62	43	67
CF Consecutive

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:Z10	Expression	=AND(COLUMNS($E:E)>=$D6,E6<>"",MIN(OFFSET(E6,,,,-$D6))>=$B6)	text	NO

 

[mahmed1]

Wow - i’ll test this and give it a go when i get access to excel

Based on your example shown that looks perfect - Your amazing and thank you...

Re second Part - How Can i amend this formula where if i had a macro to hide columns ie display every mondays then the formula to work exactly same way but instead look at visible cells (This way i can filter Mondays, Tuesdays via code and still have conditional formatting work)

 

[mahmed1]

Hi Peter - I tested the formula and it worked perfect to check against greater than or = column C target - I changed to > than rather than >= but works fine

I need to add a couple of variations to this formula where i check to see if the value is < than column C target as some metrics are bad if it goes down but if i change to < it dont work

(My column started at F and days to check were col D as is in your example and value to compare against in C)


The couple of variations i need to apply are 1 to check if the value is less than target
eg =AND(COLUMNS($F:F)>=$D11,F11<>"",MIN(OFFSET(F11,,,,-$D11))<$C11)

and 2 = check if the value < than target in column B or > target in column C (as 1 or 2 metrics needs to check to see if the value is either lower than lower bound and higher than upper bound
=AND(COLUMNS($F:F)>=$D11,F11<>"",OR(MIN(OFFSET(F11,,,,-$D11))<$B11,MIN(OFFSET(F11,,,,-$D11))>$C11))

I tried tweaking the above to formulas like listed above without any success

 

[Peter_SSs]

Can you give us some simple sample data, manually coloured, with XL2BB & explain in relation to that sample data?
I can't follow for sure what you are saying and giving formulas that don't work doesn't actually help.

 

[mahmed1]

Hi does the image below help

For metric 1 im checking to see if the value is less than -20 or > 20 and consec days >=3

Metrics 2 - 3 im checking to see > 500 and 60 and consec days >=3

Metric 4-5 im checking to see < 90 and consec days >=3

There will me many metrics so i can apply the relevant conditions to the that row criteria eg if another metric added was same as metric 1 - ill just apply the rule the same as metric 1

Hopefully this explains it better (The formula you provided works perfect for > target and >= consec days)

B, C, D, E, F

 

[Peter_SSs]

Hi does the image below help

Not really because I cannot copy/paste from it to test with.

 

[mahmed1]

Hiya

Does this help

Ive put the level of conditions on there and how it should format

	Target		Consecutive Days	Metrics	28/09/2020	29/09/2020	30/09/2020	01/10/2020	02/10/2020	03/10/2020	04/10/2020	05/10/2020	06/10/2020	07/10/2020	08/10/2020	09/10/2020	10/10/2020
<	-20.00%	20.00%	3	Metric 1	-21.00%	-21.00%	-21.00%	-21.00%	25.00%	15.00%	15.00%	15.00%	-21.00%	-21.00%	90.00%	90.00%	90.00%
	>	500	3	Metric 2	500	500	501	501	520	520	520	500	500	500	501	501	501
	<	90.00%	3	Metric 4	90.00%	90.00%	90.00%	85.00%	85.00%	85.00%	85.00%	85.00%	90.00%	85.00%	84.00%	84.00%	84.00%

 

[mahmed1]

TriggerPoints Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4
5		Target		Consecutive Days	Metrics	28/09/2020	29/09/2020	30/09/2020	01/10/2020	02/10/2020	03/10/2020	04/10/2020	05/10/2020	06/10/2020	07/10/2020	08/10/2020	09/10/2020	10/10/2020
6	<	-20.00%	20.00%	3	Metric 1	-21.00%	-21.00%	-21.00%	-21.00%	25.00%	15.00%	15.00%	15.00%	-21.00%	-21.00%	90.00%	90.00%	90.00%
7
8		>	500	3	Metric 2	500	500	501	501	520	520	520	500	500	500	501	501	501
9
10		<	90.00%	3	Metric 4	90.00%	90.00%	90.00%	85.00%	85.00%	85.00%	85.00%	85.00%	90.00%	85.00%	84.00%	84.00%	84.00%
11
Sheet1

Cell Formulas
Range	Formula
G5:R5	G5	=F5+1