Code To Highlight Rows When Data Is Missing In Column AA

[Dazzawm]

Hi. I need a code please that when it is run a matching set of cells are highlighted when there is data missing amongst group of rows. Below should explain better.

Column C Column AA

AUA618 2012	Please Obtain OE Number And Cross Refer
AUA618 2012	Please Obtain OE Number And Cross Refer
AUA618 2012	Please Obtain OE Number And Cross Refer
AUA618 3012
AUA618 3012
AUA618 3012
AUA620 2008	Please Obtain OE Number And Cross Refer
AUA620 2008	Please Obtain OE Number And Cross Refer
AUA620 2008
AUA620 2017	Please Obtain OE Number And Cross Refer
AUA620 2017
AUA620 2017
AUA620 3008	Please Obtain OE Number And Cross Refer
AUA620 3008	Please Obtain OE Number And Cross Refer
AUA620 3008
AUA620 3014
AUA620 3014	Please Obtain OE Number And Cross Refer
AUA620 3014
AUA620D 2001	Please Obtain OE Number And Cross Refer
AUA620D 2001	Please Obtain OE Number And Cross Refer
AUA620D 2001	Please Obtain OE Number And Cross Refer

As you can see sets of rows match in column C, if there is data missing in column AA then the rows need to be highlighted like below. You'll notice data is missing in most rows as 'Please Obtain OE Number And Cross Refer' is missing in most. The bottom set of 3 remain unhighlighted as data is in all three rows in Column AA. Thanks

Coulmn C Column AA

 

[Dazzawm]

No whenever data matches in C it then needs to look at AA and if data is missing from a cell then all the rows need highlighting from A to AA

Put simply if C5 to C8 match then AA5 to AA8 needs to match, or C5001 to C5020 match then AA5001 to AA5020 need to match if they do then don't highlight, if there is any blank cells in AA then highlight and so on.....

 

[DanteAmor]

I think it works, but if possible if like in the example at the bottom there is data in all 3 rows (20, 21 and 22) then can they be left not highlighted?

Now I don't understand what you want.
In my example image rows 20, 21 and 22 are not highlighted.

And also the same in rows 5, 6 and 7 there is no data in AA then they can be left not highlighted also? Basically I only want rows highlighted if there is an anomaly.

I do not understand anything. What do you mean by anomaly?

You could put the different scenarios again, which ones you want to highlight and which ones you don't.

In your original post you have them highlighted now you don't want them. You are contradicting yourself.
I ask you to create a new example, observe that you do not have errors in your examples, since that confuses the people who want to help you.

 

[Dazzawm]

Now I don't understand what you want.
In my example image rows 20, 21 and 22 are not highlighted.
View attachment 89900



I do not understand anything. What do you mean by anomaly?

You could put the different scenarios again, which ones you want to highlight and which ones you don't.

In your original post you have them highlighted now you don't want them. You are contradicting yourself.
I ask you to create a new example, observe that you do not have errors in your examples, since that confuses the people who want to help you.

When I run your code every other set of matching cells are coloured, that's not what I need. I can't put my requirements any simpler. Yes I made an error in my original post in rows 5, 6 and 7.

Look at my posts 10 and 11, may explain better.

 

[Micron]

If you create a range, format the interior colour as you would want it to be and post that maybe someone can figure it out. Your explanations might be good enough for you but not me. If you can't/won't post a sample of a sheet the way you want it, all I can say is I wish you luck but I'm out.

 

[DanteAmor]

When I run your code every other set of matching cells are coloured, that's not what I need. I can't put my requirements any simpler. Yes I made an error in my original post in rows 5, 6 and 7.

Look at my posts 10 and 11, may explain better.

I'm sorry, although I would like to, I can't continue helping you, your explanations are not clear and you don't give new examples that match your explanations.
(I myself have given more examples than you.)

 

[Dazzawm]

Once I get the forum add-in working I will post another example, this is why the original post was not that good.

 

[Dazzawm]

Below is before any sort of code.

Book1
	C																								AA
2	AUA618 2012																								Please Obtain OE Number And Cross Refer
3	AUA618 2012																								Please Obtain OE Number And Cross Refer
4	AUA618 2012
5	AUA618 3012
6	AUA618 3012
7	AUA618 3012
8	AUA620 2008																								Please Obtain OE Number And Cross Refer
9	AUA620 2008																								Please Obtain OE Number And Cross Refer
10	AUA620 2008																								Please Obtain OE Number And Cross Refer
11	AUA620 2017
12	AUA620 2017
13	AUA620 2017
14	AUA620 3008																								Please Obtain OE Number And Cross Refer
15	AUA620 3008
16	AUA620 3008
17	AUA620 3014
18	AUA620 3014
19	AUA620 3014
Sheet1

Below is the desired result.

Book1
	C																								AA
2	AUA618 2012																								Please Obtain OE Number And Cross Refer
3	AUA618 2012																								Please Obtain OE Number And Cross Refer
4	AUA618 2012
5	AUA618 3012
6	AUA618 3012
7	AUA618 3012
8	AUA620 2008																								Please Obtain OE Number And Cross Refer
9	AUA620 2008																								Please Obtain OE Number And Cross Refer
10	AUA620 2008																								Please Obtain OE Number And Cross Refer
11	AUA620 2017
12	AUA620 2017
13	AUA620 2017
14	AUA620 3008																								Please Obtain OE Number And Cross Refer
15	AUA620 3008
16	AUA620 3008
17	AUA620 3014
18	AUA620 3014
19	AUA620 3014
Sheet1

Rows C2 to C4 are highlghted because AA2 to AA4 are not all the same as 'Please Obtain OE Number And Cross Refer' is missing in one of the rows. The same is with rows C14 to C16, one row has 'Please Obtain OE Number And Cross Refer' but the other two don't so needs highlighting. All the others are not highlighted because like C5 to C7 all 3 rows in AA have nothing so they 'match' so get ignored. C8 to C10 all have the same data in all cells in AA so these also get ignored. I hope this explains better.

 

[DanteAmor]

Ok, for the same group, if all the cells of the AA are the same, then ignore, otherwise highlight.
Try this. The same technique with dictionary and arrays so that the process is very fast.

Sub HighlightRows()      'When Data Is Missing
  Dim i As Long, y As Long, fil As Long, col As Long, j As Long, k As Long, m As Long
  Dim dic As Object
  Dim a As Variant, b As Variant, ky As Variant
  Dim bln As Boolean, txt As String
  Dim rng As Range, rng1 As Range, rng2 As Range
  
  Set dic = CreateObject("Scripting.Dictionary")
    
  a = Range("C1:AA" & Range("C" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 1000)
  
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      y = y + 1
      dic(a(i, 1)) = y & "|" & 1 & "|" & a(i, 25) & "|" & False
      b(y, 1) = i
    Else
      fil = Split(dic(a(i, 1)), "|")(0)
      col = Split(dic(a(i, 1)), "|")(1)
      txt = Split(dic(a(i, 1)), "|")(2)
      bln = Split(dic(a(i, 1)), "|")(3)
      col = col + 1
      b(fil, col) = i
      If a(i, 25) <> txt Then bln = True
      dic(a(i, 1)) = fil & "|" & col & "|" & txt & "|" & bln
    End If
  Next
  
  Set rng1 = Range("C1,AA1")
  Set rng2 = Range("C1,AA1")
  
  For Each ky In dic.keys
    fil = Split(dic(ky), "|")(0)
    col = Split(dic(ky), "|")(1)
    txt = Split(dic(ky), "|")(2)
    bln = Split(dic(ky), "|")(3)
    If bln = True Then
      If k = 0 Then k = 1 Else k = 0
      For j = 1 To col
        m = b(fil, j)
        Set rng = Range("C" & m & ",AA" & m)
        If k = 0 Then Set rng1 = Union(rng1, rng) Else Set rng2 = Union(rng2, rng)
      Next
    End If
  Next
  
  Range("C:C, AA:AA").Interior.ColorIndex = xlNone
  rng1.Interior.Color = vbYellow
  rng2.Interior.Color = 16750899
  Range("C1,AA1").Interior.ColorIndex = xlNone
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Micron]

Beat me by 16 minutes but will post anyway.

Sub ConditionalFormat()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
Dim Lrow As Long, i As Integer, x As Integer
Dim str1 As String, str2 As String
Dim bolEqual As Boolean
 
Set ws = ActiveSheet
Lrow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
For i = 1 To Lrow
    str1 = ws.Range("C" & i)
    str2 = ws.Range("AA" & i)
    Set rng1 = ws.Range("C" & i)
    Set rng2 = ws.Cells.Find(str1, rng1, xlFormulas, xlWhole, xlByRows, xlPrevious, False)
        For x = rng1.Row To rng2.Row
            If ws.Range("AA" & x) = str2 Then
               bolEqual = True
            Else
               bolEqual = False
            End If
            i = i + 1
        Next
        i = i - 1
   If Not bolEqual Then ws.Range("C" & rng1.Row & ":" & "AA" & rng2.Row).Interior.Color = vbYellow
  Next
 
End Sub

 

[Micron]

Either way, I suppose code should start from something other than row 1 unless the goal is to re-evaluate the entire sheet each time. If user wanted to edit any cell that would change the outcome, then those rows would have to be re-evaluated AND my code changed to undo a yellow cell.

EDIT - to undo

     If Not bolEqual Then
         ws.Range("C" & rng1.Row & ":" & "AA" & rng2.Row).Interior.Color = vbYellow
     Else
         ws.Range("C" & rng1.Row & ":" & "AA" & rng2.Row).Interior.Color = 16777215
     End If