Highlight row based on cell value

[mike31z]

Hi, all.
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]

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]

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]

hey mike, do you need to hard code the undo? it might be easier to just select all and clear the highlight

[mike31z]

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]

nice! glad it worked out for you