Code To Highlight Rows When Data Is Missing In Column AA

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,783
Office Version
  1. 365
Platform
  1. Windows
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 2012Please Obtain OE Number And Cross Refer
AUA618 2012Please Obtain OE Number And Cross Refer
AUA618 2012Please Obtain OE Number And Cross Refer
AUA618 3012
AUA618 3012
AUA618 3012
AUA620 2008Please Obtain OE Number And Cross Refer
AUA620 2008Please Obtain OE Number And Cross Refer
AUA620 2008
AUA620 2017Please Obtain OE Number And Cross Refer
AUA620 2017
AUA620 2017
AUA620 3008Please Obtain OE Number And Cross Refer
AUA620 3008Please Obtain OE Number And Cross Refer
AUA620 3008
AUA620 3014
AUA620 3014Please Obtain OE Number And Cross Refer
AUA620 3014
AUA620D 2001Please Obtain OE Number And Cross Refer
AUA620D 2001Please Obtain OE Number And Cross Refer
AUA620D 2001Please 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
1681746649328.png
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi @Dazzawm .

Try the following conditional format:

Dante Amor
ABCDZAA
1GroupMsg
2AUA618 2012Please Obtain OE Number And Cross Refer
3AUA618 2012Please Obtain OE Number And Cross Refer
4AUA618 2012Please Obtain OE Number And Cross Refer
5AUA618 3012
6AUA618 3012
7AUA618 3012
8AUA620 2008Please Obtain OE Number And Cross Refer
9AUA620 2008Please Obtain OE Number And Cross Refer
10AUA620 2008
11AUA620 2017Please Obtain OE Number And Cross Refer
12AUA620 2017
13AUA620 2017
14AUA620 3008Please Obtain OE Number And Cross Refer
15AUA620 3008Please Obtain OE Number And Cross Refer
16AUA620 3008
17AUA620 3014
18AUA620 3014Please Obtain OE Number And Cross Refer
19AUA620 3014
20AUA620D 2001Please Obtain OE Number And Cross Refer
21AUA620D 2001Please Obtain OE Number And Cross Refer
22AUA620D 2001Please Obtain OE Number And Cross Refer
Hoja3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:C1000,AA2:AA1000Expression=COUNTIF(C:C,C2)=COUNTIFS(C:C,C2,AA:AA,"<>")textNO



--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0
I was going to suggest cf based on =AA1 = ""
Can you explain why the more complicated expression would be better, assuming it is?
Thanks.
 
Upvote 0
DanteAmor said:
Hi @Dazzawm .

Try the following conditional format:

Dante Amor
ABCDZAA
1GroupMsg
2AUA618 2012Please Obtain OE Number And Cross Refer
3AUA618 2012Please Obtain OE Number And Cross Refer
4AUA618 2012Please Obtain OE Number And Cross Refer
5AUA618 3012
6AUA618 3012
7AUA618 3012
8AUA620 2008Please Obtain OE Number And Cross Refer
9AUA620 2008Please Obtain OE Number And Cross Refer
10AUA620 2008
11AUA620 2017Please Obtain OE Number And Cross Refer
12AUA620 2017
13AUA620 2017
14AUA620 3008Please Obtain OE Number And Cross Refer
15AUA620 3008Please Obtain OE Number And Cross Refer
16AUA620 3008
17AUA620 3014
18AUA620 3014Please Obtain OE Number And Cross Refer
19AUA620 3014
20AUA620D 2001Please Obtain OE Number And Cross Refer
21AUA620D 2001Please Obtain OE Number And Cross Refer
22AUA620D 2001Please Obtain OE Number And Cross Refer
Hoja3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:C1000,AA2:AA1000Expression=COUNTIF(C:C,C2)=COUNTIFS(C:C,C2,AA:AA,"<>")textNO



--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
Click to expand...
Thanks but I really need a macro to put in my personal macro workbook. I will use on hundreds of files of different sizes with thousands of rows and also can't use helper columns.
 
Upvote 0
maybe as a start (because I have to run out and can't modify to suit a personal wb right now)
VBA Code: 
Sub ConditionalFormat()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Integer
    Set ws = ActiveSheet

    lastRow = ws.Cells(ws.Rows.Count, "AA").End(xlUp).Row
        For i = 2 To lastRow
          If ws.Cells(i, "AA") = "" Then ws.Range("A" & i & ":" & "AA" & i).Interior.Color = vbYellow
        Next
       
End Sub
 
Upvote 0
Micron said:
maybe as a start (because I have to run out and can't modify to suit a personal wb right now)
VBA Code: 
Sub ConditionalFormat()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Integer
    Set ws = ActiveSheet

    lastRow = ws.Cells(ws.Rows.Count, "AA").End(xlUp).Row
        For i = 2 To lastRow
          If ws.Cells(i, "AA") = "" Then ws.Range("A" & i & ":" & "AA" & i).Interior.Color = vbYellow
        Next
     
End Sub
Click to expand...
Thanks but that doesn't work. Almost every row is highlighted. The code needs to look at column C first to get all the rows that match then look at column AA to see that each row has data according to the matches in C.

It is almost doing the opposite to what I want as the only unhighlighted rows are the ones with data in AA, no matter what matches in C.
 
Upvote 0
Just to comment that the formula in the CF must have the sign <>
Rich (BB code): 
=COUNTIF(C:C,C2)<>COUNTIFS(C:C,C2,AA:AA,"<>")
---------------------------------------------------------------------

Dazzawm said:
Thanks but I really need a macro to put in my personal macro workbook. I will use on hundreds of files of different sizes with thousands of rows and also can't use helper columns.
Click to expand...
If you have thousands of records, then it might be worth using a dictionary and arrays to speed up the process.

Just to clarify, in your example data 3,4 and 5 are different than data 1,2 and 3:
1681759463280.png


The macro will start reading the data from row 1, assuming row 1 has the headers.

VBA Code: 
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
  Dim 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
      If a(i, 25) = "" Then bln = True Else bln = False
      dic(a(i, 1)) = y & "|" & 1 & "|" & bln
      b(y, 1) = i
    Else
      fil = Split(dic(a(i, 1)), "|")(0)
      col = Split(dic(a(i, 1)), "|")(1)
      bln = Split(dic(a(i, 1)), "|")(2)
      col = col + 1
      b(fil, col) = i
      If bln = False Then
        If a(i, 25) = "" Then bln = True
      End If
      dic(a(i, 1)) = fil & "|" & col & "|" & 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)
    bln = Split(dic(ky), "|")(2)
    If bln = True Then
      If k = 0 Then k = 1 Else k = 0
      For j = 1 To col
        m = b(fil, j)
        If k = 0 Then
          Set rng1 = Union(rng1, Range("C" & m & ",AA" & m))
        Else
          Set rng2 = Union(rng2, Range("C" & m & ",AA" & m))
        End If
      Next
    End If
  Next
 
  Range("C:C, AA:AA").Interior.ColorIndex = xlNone
  rng1.Interior.Color = vbYellow
  rng2.Interior.Color = vbBlue
  Range("C1,AA1").Interior.ColorIndex = xlNone
End Sub


The macro will also alternate colors. Example:
1681759792491.png



--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------
 
Upvote 0
DanteAmor said:
Just to comment that the formula in the CF must have the sign <>
Rich (BB code): 
=COUNTIF(C:C,C2)<>COUNTIFS(C:C,C2,AA:AA,"<>")
---------------------------------------------------------------------


If you have thousands of records, then it might be worth using a dictionary and arrays to speed up the process.

Just to clarify, in your example data 3,4 and 5 are different than data 1,2 and 3:
View attachment 89894

The macro will start reading the data from row 1, assuming row 1 has the headers.

VBA Code: 
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
  Dim 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
      If a(i, 25) = "" Then bln = True Else bln = False
      dic(a(i, 1)) = y & "|" & 1 & "|" & bln
      b(y, 1) = i
    Else
      fil = Split(dic(a(i, 1)), "|")(0)
      col = Split(dic(a(i, 1)), "|")(1)
      bln = Split(dic(a(i, 1)), "|")(2)
      col = col + 1
      b(fil, col) = i
      If bln = False Then
        If a(i, 25) = "" Then bln = True
      End If
      dic(a(i, 1)) = fil & "|" & col & "|" & 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)
    bln = Split(dic(ky), "|")(2)
    If bln = True Then
      If k = 0 Then k = 1 Else k = 0
      For j = 1 To col
        m = b(fil, j)
        If k = 0 Then
          Set rng1 = Union(rng1, Range("C" & m & ",AA" & m))
        Else
          Set rng2 = Union(rng2, Range("C" & m & ",AA" & m))
        End If
      Next
    End If
  Next
 
  Range("C:C, AA:AA").Interior.ColorIndex = xlNone
  rng1.Interior.Color = vbYellow
  rng2.Interior.Color = vbBlue
  Range("C1,AA1").Interior.ColorIndex = xlNone
End Sub


The macro will also alternate colors. Example:
View attachment 89896


--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------
Click to expand...
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? 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.
 
Upvote 0
I thought the requirement was to highlight a row (and I took that to mean not the entire row all the way out to XFD) wherever AA was blank as below - but I guess not.
1681762596264.png
 
Upvote 0
Micron said:
I thought the requirement was to highlight a row (and I took that to mean not the entire row all the way out to XFD) wherever AA was blank as below - but I guess not.
View attachment 89898
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Dazzawm
  • Question Question
Code To Highlight Row When Data Is In AA
2 3
Replies
23
Views
1K
CSmith
CSmith
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
390
ChrisMac1
ChrisMac1
D
  • Solved
How To Reference Last Row With Data In A Column?
Replies
8
Views
409
Dhira
D
J
  • Question Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
339
JMC57
J
C
  • Question Question
Use Row Data to Create New Sheet from Template and Reference Data
Replies
0
Views
636
ChrisMas92
C

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top