Automatic conditional formatting from a list
 

Hi All

I need a macro that colours cells if the product name in column A on it's
row matches any in the product list.

There is a list of product names in C17:End(xlUp).Row, that have different
backgrounds applied to each cell in the list.

The macro looks at Column A in the worksheet & compares it to the product
name list.

Where it finds a product name match, it goes across to columns I:BJ (on the
matched row) and applies the product name's background colour to any "active
cells" (i.e. value 0).

The following code does this but it's applies the background to the same
range as it's searching, and that data is a column - not a row...
How do I adapt it?

FYI
Z = product list starting from row 74 in the old code and already formatted
in different backgrounds
My range is C17:End(xlUp)

C = is the data being searched and having the background applied too - but
it's a column.
My range to search is column "A44:A200"
My range to apply the background too is "matched row between I:BJ".

Currently it's a Worksheet_SelectionChange type macro.
Will this make the file run slow? i.e. will it need to re-calculate &
re-apply alot?

If yes, can it be a normal macro where the user clicks the button to update
the cell formats on all rows within a range (e.g. 44:200) - but it will have
to clear the backgrounds before running each time.

______________________
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Integer
Dim Z As Integer

For Z = 74 To Cells(Rows.Count, "Z").End(xlUp).Row
For Cell = 95 To Cells(Rows.Count, "C").End(xlUp).Row
If Cells(Cell, "C").Value Like "*" & Range("Z" & Z).Value & "*" Then
Cells(Cell, "C").Interior.Color = Range("Z" & Z).Interior.Color
End If
Next Cell
Next Z
End Sub

--
Thank for your help in advance
BeSmart