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]

Hi @Peter_SSs

Please ket me know if you are able to copy the latest image over

I tried to use the copy method using the mr excel tool

Apologies if its still not right

 

[mahmed1]

Hi Peter - image i pasted has no conditional formatting but I manually formatted how it should look

Your formula for greater than the target value works fine - need tweaking slightly to work with less than target value and also for metric 1 < than target value in B and > target value in C)

 

[Peter_SSs]

Please ket me know if you are able to copy the latest image over

Yes, I can copy the data from XL2BB, thanks.

See if this works for you. It assumes that ..
'Metric 1' always means less than the column B value or greater than the column C value
'Metric 2' always means greater than the column C value
'Metric 4' always means less than the column C value

mahmed1.xlsm
	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/20	29/09/20	30/09/20	1/10/20	2/10/20	3/10/20	4/10/20	5/10/20	6/10/20	7/10/20	8/10/20	9/10/20	10/10/20
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%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F6:R12	Expression	=AND(COLUMNS($F:F)>=$D6,F6<>"",CHOOSE(RIGHT($E6,1),COUNTIFS(OFFSET(F6,,,,-$D6),">="&$B6,OFFSET(F6,,,,-$D6),"<="&$C6)=0,MIN(OFFSET(F6,,,,-$D6))>$C6,,MAX(OFFSET(F6,,,,-$D6))<$C6))	text	NO

 

[mahmed1]

Thank you - works superb

In regards to the changing the formula to apply to visible cells (Eg i filter all columns that is a monday) - would the formula work or would i need to tweak it?

If it doesn’t- would you advise to use VBA or is it better to amend formula to incorporate in visible cells?

 

[Peter_SSs]

I don't think the formula could readily be adapted to deal with ignoring hidden columns (though I would be happy for somebody to prove me wrong with that).
Therefore I think that you would need vba. My attempt, for the particular sheet layout as shown in post #10 is

Sub Highlight_Metrics()
  Dim LastCol As Long, LastRow As Long, r As Long, c As Long, Consec As Long, k As Long
  Dim dLow As Double, dHigh As Double
  Dim sTemp As String, sHighlight As String
  
  LastCol = Cells(5, Columns.Count).End(xlToLeft).Column
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  Range("F6", Cells(LastRow, LastCol)).Interior.Color = xlNone
  For r = 6 To LastRow
    sHighlight = vbNullString
    sTemp = vbNullString
    k = 0
    If IsNumeric(Range("B" & r).Value) Then dLow = Range("B" & r).Value
    dHigh = Range("C" & r).Value
    Consec = Range("D" & r).Value
    Select Case Range("E" & r).Value
      Case "Metric 1"
        For c = 6 To LastCol
          If Not Columns(c).Hidden Then
            If Cells(r, c).Value < dLow Or Cells(r, c).Value > dHigh Then
              k = k + 1
              sTemp = sTemp & "," & Cells(r, c).Address(0, 0)
            Else
              If k >= Consec Then
                sHighlight = sHighlight & sTemp
                sTemp = vbNullString
              End If
              k = 0
            End If
          End If
        Next c
      Case "Metric 2"
        For c = 6 To LastCol
          If Not Columns(c).Hidden Then
            If Cells(r, c).Value > dHigh Then
              k = k + 1
              sTemp = sTemp & "," & Cells(r, c).Address(0, 0)
            Else
              If k >= Consec Then
                sHighlight = sHighlight & sTemp
                sTemp = vbNullString
              End If
              k = 0
            End If
          End If
        Next c
      Case "Metric 4"
        For c = 6 To LastCol
          If Not Columns(c).Hidden Then
            If Cells(r, c).Value < dHigh Then
              k = k + 1
              sTemp = sTemp & "," & Cells(r, c).Address(0, 0)
            Else
              If k >= Consec Then
                sHighlight = sHighlight & sTemp
                sTemp = vbNullString
              End If
              k = 0
            End If
          End If
        Next c
    End Select
    If k >= Consec Then sHighlight = sHighlight & sTemp
    If Len(sHighlight) > 0 Then Range(Mid(sHighlight, 2)).Interior.Color = vbYellow
  Next r
End Sub

My results with a few random columns hidden are:

mahmed1.xlsm
	A	B	C	D	E	F	G		I	J		L		N	O	P	Q
1
2
3
4
5		Target		Consecutive Days	Metrics	28/09/20	29/09/20		1/10/20	2/10/20		4/10/20		6/10/20	7/10/20	8/10/20	9/10/20
6	<	-20.00%	20.00%	3	Metric 1	-21.00%	-21.00%		-21.00%	25.00%		15.00%		-21.00%	-21.00%	90.00%	90.00%
7
8		>	500	3	Metric 2	500	500		501	520		520		500	500	501	501
9
10		<	90.00%	3	Metric 4	90.00%	85.00%		85.00%	85.00%		85.00%		90.00%	85.00%	84.00%	84.00%
Sheet2

 

[mahmed1]

Thank you so much... il give that a go

Couple of questions from just looking at your example

1) Does the code mean i can get rid of any existing conditional formatting (ie the formula you provided for conditional formatting)?

2) Based on the yellow highlighted cells, just having a look to see what should have been highlighted

For Metric 1 with the visible cells the 3rd occurrence of where the condition >= consec days of 3 is 1st of Oct based on the visible cells

 

[Peter_SSs]

1) Does the code mean i can get rid of any existing conditional formatting (ie the formula you provided for conditional formatting)?

Yes

2) Based on the yellow highlighted cells, just having a look to see what should have been highlighted

For Metric 1 with the visible cells the 3rd occurrence of where the condition >= consec days of 3 is 1st of Oct based on the visible cells

Yes again. I forgot to only highlight from when the consecutive cell count had been reached, not right from the beginning.

See if this is better. (It actually makes the code a fraction shorter )

Sub Highlight_Metrics_v2()
  Dim LastCol As Long, LastRow As Long, r As Long, c As Long, Consec As Long, k As Long
  Dim dLow As Double, dHigh As Double
  Dim sHighlight As String
  
  LastCol = Cells(5, Columns.Count).End(xlToLeft).Column
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  Range("F6", Cells(LastRow, LastCol)).Interior.Color = xlNone
  For r = 6 To LastRow
    sHighlight = vbNullString
    k = 0
    If IsNumeric(Range("B" & r).Value) Then dLow = Range("B" & r).Value
    dHigh = Range("C" & r).Value
    Consec = Range("D" & r).Value
    Select Case Range("E" & r).Value
      Case "Metric 1"
        For c = 6 To LastCol
          If Not Columns(c).Hidden Then
            If Cells(r, c).Value < dLow Or Cells(r, c).Value > dHigh Then
              k = k + 1
              If k >= Consec Then sHighlight = sHighlight & "," & Cells(r, c).Address(0, 0)
            Else
              k = 0
            End If
          End If
        Next c
      Case "Metric 2"
        For c = 6 To LastCol
          If Not Columns(c).Hidden Then
            If Cells(r, c).Value > dHigh Then
              k = k + 1
              If k >= Consec Then sHighlight = sHighlight & "," & Cells(r, c).Address(0, 0)
            Else
              k = 0
            End If
          End If
        Next c
      Case "Metric 4"
        For c = 6 To LastCol
          If Not Columns(c).Hidden Then
            If Cells(r, c).Value < dHigh Then
              k = k + 1
              If k >= Consec Then sHighlight = sHighlight & "," & Cells(r, c).Address(0, 0)
            Else
              k = 0
            End If
          End If
        Next c
    End Select
    If Len(sHighlight) > 0 Then Range(Mid(sHighlight, 2)).Interior.Color = vbYellow
  Next r
End Sub

mahmed1.xlsm
	A	B	C	D	E	F	G		I	J		L		N	O	P	Q
1
2
3
4
5		Target		Consecutive Days	Metrics	28/09/20	29/09/20		1/10/20	2/10/20		4/10/20		6/10/20	7/10/20	8/10/20	9/10/20
6	<	-20.00%	20.00%	3	Metric 1	-21.00%	-21.00%		-21.00%	25.00%		15.00%		-21.00%	-21.00%	90.00%	90.00%
7
8		>	500	3	Metric 2	500	500		501	520		520		500	500	501	501
9
10		<	90.00%	3	Metric 4	90.00%	85.00%		85.00%	85.00%		85.00%		90.00%	85.00%	84.00%	84.00%
Sheet2

 

[mahmed1]

Absolute Legend

thank you so much

 

[Peter_SSs]

You're welcome.

 

[mahmed1]

I appreciate all your help and cant thank you enough

i dont how hard this will be in to add and whether i can easily tweak your code to achieve this but it will be awesome if i can do this

if i added 2 more consecutive days columns added After Column D (so D, E, F)

so in I might have , 3,5,7
Is it possible to have the exact same code as above but if the consec days hits >= to D then highlight 1 colour and if it hits >= E then highlight a another colour and if >= F then say a Red colour

what im trying to do Is use your exact same code and logic but if it hits more than x amount amount consec days then highlight a different colour

so D, E, F will have different days to check

sorry to be a pain but this will be awesome of i can tweak your code to achieve this

thank you once again - uve been so helpful