VBA - highlight consecutive

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Bump
Any help would be appreciated
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
Not sure about the second part but for the first part, does this Conditional Formatting do what you want?

20 10 20.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZ
6703696768717071775567787860617071777875
750780706078789080205050505050505050606562701015
8482504847405459396164346850586050316054624367
CF Consecutive
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E6:Z10Expression=AND(COLUMNS($E:E)>=$D6,E6<>"",MIN(OFFSET(E6,,,,-$D6))>=$B6)textNO
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

1603185054912.png
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hiya

Does this help

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

TargetConsecutive DaysMetrics28/09/202029/09/202030/09/202001/10/202002/10/202003/10/202004/10/202005/10/202006/10/202007/10/202008/10/202009/10/202010/10/2020
<-20.00%20.00%3Metric 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%
>5003Metric 2500500501501520520520500500500501501501
<90.00%3Metric 490.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

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
TriggerPoints Test.xlsx
ABCDEFGHIJKLMNOPQR
1
2
3
4
5TargetConsecutive DaysMetrics28/09/202029/09/202030/09/202001/10/202002/10/202003/10/202004/10/202005/10/202006/10/202007/10/202008/10/202009/10/202010/10/2020
6<-20.00%20.00%3Metric 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>5003Metric 2500500501501520520520500500500501501501
9
10<90.00%3Metric 490.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
RangeFormula
G5:R5G5=F5+1
 

Watch MrExcel Video

Forum statistics

Threads
1,113,939
Messages
5,545,110
Members
410,658
Latest member
kris_friis
Top