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]

Hi @Dazzawm.


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
--------------

That does exactly as I need, much obliged squire.

 

[Tom.Jones]

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

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

In your formula is not necessary that IF, could be only:

in CF =and(not(isblank($aa2), $aa2="DATA")

If I understand correctly what op wants then:
in CF =and(Unique($c2), $aa2="DATA")

 

[awoohaw]

Does this work:

mr excel questions 25.xlsm
	C																								AA	AB	AC
1	CatCode
2	AUA314 1022																								Data	TRUE
3	AUA618 2012																									FALSE
4	AUA618 2012																									FALSE
5	AUA618 2012																									FALSE
6	AUA618 3012																									FALSE
7	AUA620 2008																									FALSE
8	AUA620 2017																								Data	TRUE
9	AUA620 3008																									FALSE
10	AUA620 3008																									FALSE
11	AUA620 3014																									FALSE
dazzawm

Cell Formulas
Range	Formula
AB2:AB11	AB2	=IF($AA2<>"",COUNTIF($C$2:$C$11,C$2)=1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
3:11	Expression	=IF($AA3<>"",COUNTIF($C$2:$C$11,$C3)=1)	text	NO
2:2	Expression	=IF($AA2<>"",COUNTIF($C$2:$C$11,$C2)=1)	text	NO

 

[CSmith]

In your formula is not necessary that IF, could be only:

in CF =and(not(isblank($aa2), $aa2="DATA")

If I understand correctly what op wants then:
in CF =and(Unique($c2), $aa2="DATA")

No they later said could be any data so would be the OR condition, and true on the IF statement, I usually add for readability . . .