VBA code please!

[kidra98]

Hi VBA experts and looking for your kind help. I have a file with about 4000 lines and has some columns and I have given an example below the first set of data is good, 2nd set of data is not good, 3rd set of data is good and the fourth one is not good. Is there any way Excel can highlight looping through the title based on the condition 1-5 should be in sequence and based on the condition explained in column c? Is this possible either by conditional formatting or VBA code. Thank you very much for your help and time. Much appreciated.

 

[DanteAmor]

Hi and Welcome to MrExcel.

In your actual data, after the title "Apple" do you have a hyphen, the word "step" and a number?
Or do you only have "Apple"?

 

[kidra98]

Hi and Welcome to MrExcel.

In your actual data, after the title "Apple" do you have a hyphen, the word "step" and a number?
Or do you only have "Apple"?


View attachment 51407

Hi Dante,
Apologies for the late reply. Yes, I do have a hyphen and the word step and another column with step 1 and step 2, etc. This report is downloaded from the internal app of the company. Thank you for your reply and I appreciate your comments. have a great one. Regards

 

[DanteAmor]

Try this:

Sub highlight_title()
  Dim stopen As Boolean, notok As Boolean
  Dim a As Variant, it As Variant
  Dim dic As Object
  Dim tit As String
  Dim i As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  dic.comparemode = vbTextCompare
  
  With Range("A1", Range("C" & Rows.Count).End(3))
    a = .Value
    .Interior.Color = xlNone
  End With
  
  For i = 2 To UBound(a, 1)
    If a(i, 1) <> "" Then
      tit = Split(a(i, 1), "-")(0)
      If Not dic.exists(tit) Then
        If LCase(a(i, 3)) = LCase("Open") Then stopen = True Else stopen = False
        dic(tit) = i & "|" & i & "|" & stopen & "|" & True
      Else
        stopen = Split(dic(tit), "|")(2)
        notok = Split(dic(tit), "|")(3)
        If stopen Then
          If LCase(a(i, 3)) = LCase("Completed") Then 'error
            dic(tit) = Split(dic(tit), "|")(0) & "|" & i & "|" & True & "|" & False
          Else
            dic(tit) = Split(dic(tit), "|")(0) & "|" & i & "|" & True & "|" & notok
          End If
        Else
          If LCase(a(i, 3)) = LCase("Open") Then stopen = True Else stopen = False
          dic(tit) = Split(dic(tit), "|")(0) & "|" & i & "|" & stopen & "|" & notok
        End If
      End If
    End If
  Next
  For Each it In dic.items
    If Split(it, "|")(3) = False Then
      Range("A" & Split(it, "|")(0) & ":A" & Split(it, "|")(1)).Interior.Color = vbYellow
    End If
  Next
End Sub

 

[kidra98]

Try this:

Sub highlight_title()
  Dim stopen As Boolean, notok As Boolean
  Dim a As Variant, it As Variant
  Dim dic As Object
  Dim tit As String
  Dim i As Long
 
  Set dic = CreateObject("Scripting.Dictionary")
  dic.comparemode = vbTextCompare
 
  With Range("A1", Range("C" & Rows.Count).End(3))
    a = .Value
    .Interior.Color = xlNone
  End With
 
  For i = 2 To UBound(a, 1)
    If a(i, 1) <> "" Then
      tit = Split(a(i, 1), "-")(0)
      If Not dic.exists(tit) Then
        If LCase(a(i, 3)) = LCase("Open") Then stopen = True Else stopen = False
        dic(tit) = i & "|" & i & "|" & stopen & "|" & True
      Else
        stopen = Split(dic(tit), "|")(2)
        notok = Split(dic(tit), "|")(3)
        If stopen Then
          If LCase(a(i, 3)) = LCase("Completed") Then 'error
            dic(tit) = Split(dic(tit), "|")(0) & "|" & i & "|" & True & "|" & False
          Else
            dic(tit) = Split(dic(tit), "|")(0) & "|" & i & "|" & True & "|" & notok
          End If
        Else
          If LCase(a(i, 3)) = LCase("Open") Then stopen = True Else stopen = False
          dic(tit) = Split(dic(tit), "|")(0) & "|" & i & "|" & stopen & "|" & notok
        End If
      End If
    End If
  Next
  For Each it In dic.items
    If Split(it, "|")(3) = False Then
      Range("A" & Split(it, "|")(0) & ":A" & Split(it, "|")(1)).Interior.Color = vbYellow
    End If
  Next
End Sub

Th

 

[kidra98]

Thank you very much, Dante. I will try to run this code and will get back to you end of the day. Thank you, Sir, much appreciated.

 

[kidra98]

Hi Dante Sir,
Thank you very much, and it works perfectly and flawlessly. Much appreciate your time and help. Seriously it was crazy code and thank you.

 

[DanteAmor]

Hello Kidra, you marked your solution as an answer. You must mark the post that really solved your problem.

I'm glad to help you. Thanks for the feedback.