PDA

View Full Version : [SOLVED] Highlight row based on cell value



mike31z
05-22-2012, 11:43 AM
Hi, all. :dunno
What I would like to achieve is to highlight (a light pastel color) a row from column A to P if the value of 2 is in column AC for that row.

I would also highlight (a different light pastel color) a row from column A to P if the value of 4 is in column AC for that row.

I would also need to turn off the highlighting when done. Conditional formating would not work because the full worksheet is sorted multiple ways.

I can then copy the code and change AC to AA for a different sort.

I have attached a sample xls file.

From the attached XL file you can see that column AC is one sort column, Column AA is another sort column. AC and AA are Vlookup columns all the other columns are actual values.

I use excel 2000 but have access to excel2007 if needed.

CatDaddy
05-22-2012, 11:51 AM
For each cell in range("AC2:AC" & Range("A" & Rows.Count).End(xlUp).Row)
r = cell.row
if cell.Value = 2 Then
Range("A" & r & ":P" & r).Interior.ColorIndex = 36
elseif cell.Value = 4 Then
Range("A" & r & ":P" & r).Interior.ColorIndex = 43
End if
Next cell

mike31z
05-22-2012, 02:15 PM
Thank you that worked on my first test and I can figure out how to undue the highlighting.

That why I love this forum. Someone is always willing to help if you can explain the problem.

Mike In Wisconsin

CatDaddy
05-22-2012, 02:28 PM
hey mike, do you need to hard code the undo? it might be easier to just select all and clear the highlight

mike31z
05-23-2012, 06:41 AM
Thanks here is what I used and it works.


Columns("A:P").Select
Selection.Interior.ColorIndex = xlNone


I insert this on top of my other sort macro to clear previous colors and then sort using a different column and the end of the I insert your code modifying the column letters.

Thanks again

CatDaddy
05-23-2012, 11:16 AM
nice! glad it worked out for you