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