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.

 

[Anfinsen]

Hey Dazz,

Trying using this. it will automatically highlight the entire row in yellow, when data exists in Column C and Column AA

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim c As Range, sh1 As Worksheet
  Set sh1 = Sheets("Sheet1") 'replace sheet1 with your sheet name
  For Each c In sh1.Range("C2", sh1.Cells(Rows.Count, "C").End(xlUp)) 'this looks for a value in column C
    If c.Offset(0, 24) <> "" Then ' this looks for a value in coumn AA, which is 24 offset from C
      c.EntireRow.Interior.Color = vbYellow
    End If
  Next
End Sub

 

[CSmith]

Use conditional formatting to highlight the entire row starting @ row 2 with formula:

=IF(OR(NOT(ISBLANK($AA2)),"Data"=$AA2),TRUE,FALSE)

 

[Dazzawm]

Hey Dazz,

Trying using this. it will automatically highlight the entire row in yellow, when data exists in Column C and Column AA

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim c As Range, sh1 As Worksheet
  Set sh1 = Sheets("Sheet1") 'replace sheet1 with your sheet name
  For Each c In sh1.Range("C2", sh1.Cells(Rows.Count, "C").End(xlUp)) 'this looks for a value in column C
    If c.Offset(0, 24) <> "" Then ' this looks for a value in coumn AA, which is 24 offset from C
      c.EntireRow.Interior.Color = vbYellow
    End If
  Next
End Sub

Thanks but that's not what I am after. This highlights every row that has data in AA, I need it to highlight just single rows.

 

[Dazzawm]

Use conditional formatting to highlight the entire row starting @ row 2 with formula:

=IF(OR(NOT(ISBLANK($AA2)),"Data"=$AA2),TRUE,FALSE)

Thanks but I need a code not a formula, also the word data is as an example. AA could contain any words.

 

[CSmith]

Thanks but I need a code not a formula, also the word data is as an example. AA could contain any words.

Yes, is why I used the

OR(NOT(ISBLANK(Cell)))

 

[Dazzawm]

Any ideas on this anyone please?

 

[Anfinsen]

Thanks but that's not what I am after. This highlights every row that has data in AA, I need it to highlight just single rows.

I am not sure I understand what you are asking for. You want Any row, where data exists in both Columns C and AA? Your statement

"I need it to highlight just single rows."

is a tad confusing.

 

[Dazzawm]

I am not sure I understand what you are asking for. You want Any row, where data exists in both Columns C and AA? Your statement

I never said any row that has data in C. I said it needs to look at C to distinguish whether it is a single row or it may be a group of rows that are the same. Please look at my original post with example.

 

[Anfinsen]

I never said any row that has data in C. I said it needs to look at C to distinguish whether it is a single row or it may be a group of rows that are the same. Please look at my original post with exaSo i

I never said any row that has data in C. I said it needs to look at C to distinguish whether it is a single row or it may be a group of rows that are the same. Please look at my original post with example.

So you want a highlight every time the first 6 characters of the data in column C changes? Let me as this a different way, what makes the "groups of rows" the same?