Code To Highlight Row When Data Is In AA

[Dazzawm]

Hi. I need a code please that will highlight a single row according to C when there is data in AA. As you can see below rows 2, 6, 7 ,8 and 11 are single rows (groups of more than one row can be ignored like 3-5 and 9-10)

Book1
	C																								AA
1	CatCode
2	AUA314 1022																								Data
3	AUA618 2012
4	AUA618 2012
5	AUA618 2012
6	AUA618 3012
7	AUA620 2008
8	AUA620 2017																								Data
9	AUA620 3008
10	AUA620 3008
11	AUA620 3014
Sheet1

Below you can see rows 2 and 8 have data in AA so need highlighting, rows 6, 7 and 11 have no data in AA so don't need highlighting. It will be used on various files up to 100,000 rows and there may be groups of rows matching in C up to about 20. I have hidden other columns for clarity.

Book1
	C																								AA
2	AUA314 1022																								Data
3	AUA618 2012
4	AUA618 2012
5	AUA618 2012
6	AUA618 3012
7	AUA620 2008
8	AUA620 2017																								Data
9	AUA620 3008
10	AUA620 3008
11	AUA620 3014
Sheet1

Thanks.

 

[Dazzawm]

So you want a highlight every time the first 6 characters of the data in column C changes?

Any unique cell in C, then needs to look at AA to see if there is data in it. If so highlight that row.

 

[Anfinsen]

Any unique cell in C, then needs to look at AA to see if there is data in it. If so highlight that row.

I see you posted a different thread, with a similar question, I assume this is handled then?

 

[Dazzawm]

I see you posted a different thread, with a similar question, I assume this is handled then?

I did but the guy that solved that wouldn't help with this for some reason, and said to start another thread.

 

[Dazzawm]

I really didn't think this code was going to be too difficult for someone to do.

 

[Anfinsen]

Hey Dazz, without a sample workbook, and clear understanding of how the data is generated in the first place, this is what I came up with.
Assuming Column AB doesn't have data already in it, you need to add a "helper" column there to identify when the value in Column C changes (this identifies when a "new group" starts")
The formula to add to Cell AB2 is this

=IF(C2<>C1,"NEW","")

then insert this code into your workbook. this will only work when Values in Column AA are entered.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("AA:AA")) Is Nothing Then
     
  Dim c As Range, sh1 As Worksheet
  Set sh1 = Sheets("Sheet1")
  For Each c In sh1.Range("AB2", sh1.Cells(Rows.Count, "AB").End(xlUp))
    If c.Value <> "NEW" And c.Offset(-1, 0) <> "" Then
      c.Offset(-1, 0).EntireRow.Interior.ColorIndex = 6
    End If
  Next
  End If
End Sub

 

[Dazzawm]

Hey Dazz, without a sample workbook, and clear understanding of how the data is generated in the first place, this is what I came up with.
Assuming Column AB doesn't have data already in it, you need to add a "helper" column there to identify when the value in Column C changes (this identifies when a "new group" starts")
The formula to add to Cell AB2 is this

=IF(C2<>C1,"NEW","")

then insert this code into your workbook. this will only work when Values in Column AA are entered.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("AA:AA")) Is Nothing Then
    
  Dim c As Range, sh1 As Worksheet
  Set sh1 = Sheets("Sheet1")
  For Each c In sh1.Range("AB2", sh1.Cells(Rows.Count, "AB").End(xlUp))
    If c.Value <> "NEW" And c.Offset(-1, 0) <> "" Then
      c.Offset(-1, 0).EntireRow.Interior.ColorIndex = 6
    End If
  Next
  End If
End Sub

Thanks but I can't have a helper column in my data, sorry to be a pain

 

[Dazzawm]

Also I will be wanting to put the code in my personal macro workbook to use on hundreds of different files, so adding helper columns is not viable.

 

[jdellasala]

Can you explain why VBA is necessary when Conditional Formatting can almost certainly do what you need?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	CatCode	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	More
2	AUA314 1022																										Data
3	AUA618 2012
4	AUA618 2012
5	AUA618 2012
6	AUA618 3012
7	AUA620 2008
8	AUA620 2017																										Data
9	AUA620 3008
10	AUA620 3008
11	AUA620 3014
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:AA11	Expression	=$AA2<>""	text	NO

And BTW, that will work regardless of how many columns you add or removed between A and AA. If you don't want the columns between A and AA to be highlighted, just adjust the Applies To range.

 

[Dazzawm]

Can you explain why VBA is necessary when Conditional Formatting can almost certainly do what you need?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	CatCode	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	More
2	AUA314 1022																										Data
3	AUA618 2012
4	AUA618 2012
5	AUA618 2012
6	AUA618 3012
7	AUA620 2008
8	AUA620 2017																										Data
9	AUA620 3008
10	AUA620 3008
11	AUA620 3014
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:AA11	Expression	=$AA2<>""	text	NO

And BTW, that will work regardless of how many columns you add or removed between A and AA. If you don't want the columns between A and AA to be highlighted, just adjust the Applies To range.

Because I've asked for a macro. As I've said I have hundreds of files with hundreds of thousands of rows. Formulas and conditional formatting isn't a user friendly option.

 

[DanteAmor]

Hi @Dazzawm.

will highlight a single row according to C when there is data in AA

I hope I understand your request correctly, try the following code:

Sub Highlight_Row()
  Dim dic As Object, rng As Range, a, ky, i&
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("C1:AA" & Range("C" & Rows.Count).End(3).Row).Value2
  Set rng = Range("C1")
  
  For i = 2 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      'Only consider when there is data in AA
      If a(i, 25) = "" Then dic(a(i, 1)) = 0 Else dic(a(i, 1)) = i
    Else
      dic(a(i, 1)) = 0
    End If
  Next
  
  For Each ky In dic.keys
    If dic(ky) <> 0 Then
      Set rng = Union(rng, Range("A" & dic(ky) & ":AA" & dic(ky)))
    End If
  Next
  rng.Interior.Color = vbYellow
  Range("C1").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
--------------