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
 

mahmed1

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

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mahmed1

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
1
2
3
4
5TargetConsecutive DaysMetrics28/09/2029/09/2030/09/201/10/202/10/203/10/204/10/205/10/206/10/207/10/208/10/209/10/2010/10/20
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%
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F6:R12Expression=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))textNO
 

mahmed1

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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
ABCDEFGIJLNOPQ
1
2
3
4
5TargetConsecutive DaysMetrics28/09/2029/09/201/10/202/10/204/10/206/10/207/10/208/10/209/10/20
6<-20.00%20.00%3Metric 1-21.00%-21.00%-21.00%25.00%15.00%-21.00%-21.00%90.00%90.00%
7
8>5003Metric 2500500501520520500500501501
9
10<90.00%3Metric 490.00%85.00%85.00%85.00%85.00%90.00%85.00%84.00%84.00%
Sheet2
 

mahmed1

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. :oops:

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

VBA Code:
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
ABCDEFGIJLNOPQ
1
2
3
4
5TargetConsecutive DaysMetrics28/09/2029/09/201/10/202/10/204/10/206/10/207/10/208/10/209/10/20
6<-20.00%20.00%3Metric 1-21.00%-21.00%-21.00%25.00%15.00%-21.00%-21.00%90.00%90.00%
7
8>5003Metric 2500500501520520500500501501
9
10<90.00%3Metric 490.00%85.00%85.00%85.00%85.00%90.00%85.00%84.00%84.00%
Sheet2
 

mahmed1

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

thank you so much
 

mahmed1

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

Watch MrExcel Video

Forum statistics

Threads
1,113,942
Messages
5,545,115
Members
410,660
Latest member
marciabkin
Top