Highlighting specific data

[zombiemaster]

I have a workbook with data in columns A through Y. I want to filter by the word "UNKNOWN" in column X and highlight those rows yellow.

I thought I could filter by that phrase and then find the top of the range by using startRow$, then find the bottom of the range by using endRow$ then selecting that range and doing the highlight...but I obviously don't know what I'm doing because it's not working:

Sub FilterHighlight()
   Range("A1").Select
   Selection.End(xlDown).Select
   startRow$ = ActiveCell.Row
   Range("A100000").Select
   endRow$ = ActiveCell.Row
   Range("A" + startRow$:"Y" + endRow$.Select
End Sub

Help is appreciated - I know it's probably simple but right now, not to me...
~ZM~

 

[Joe4]

Why not just use Conditional Formatting?
You can easily highlight the whole row based on the entry in just one cell, i.e.
Select all your rows, starting with Row 1, go to Conditional Formatting, and enter this Conditional Formatting formula:
=$X1="UNKNOWN"
and select your formatting color.

By using the "$" in front of the "X", it locks that column down, but allows the rows to flow freely. So, all of row 2 will look at X2, all of row 3 will look at X3, etc,

 

[zombiemaster]

Thanks for your response and I'm sorry if I didn't give much detail. If it helps, this is a very small part of a much larger VBA project - I copy data from other workbooks and tabs, do vlookups, save to external LAN locations, etc. This color-coding is the last step that I'm trying to have the VBA accomplish before doing a final save for the department to use for their weekly processing.

I am vaguely familiar with conditional formatting outside of VBA but not within it...I didn't know you could do it this way! Thank you!!

~ZM~

 

[Rick Rothstein]

IF Column Y does not contain formulas, then this macro could be used to color the desired cells yellow...

[table="width: 500"]
[tr]
	[td]Sub HighlightUNKNOWN()
  Columns("Y").Replace "UNKNOWN", "=UNKNOWN", xlWhole, , False, , False, False
  Intersect(Columns("Y").SpecialCells(xlFormulas).EntireRow, Columns("A:Y")).Interior.Color = vbYellow
  Columns("Y").Replace "=UNKNOWN", "UNKNOWN"
End Sub[/td]
[/tr]
[/table]

 

[zombiemaster]

That's pretty slick, Rick! Thank you!!

~ZM~