thanks Mike
That works great - although with the size of my data it's not going to be
workable for the users (constantly updating with every enter)...
So I've adapted a similar code to run as a button activated macro.
However, I'll definitely use this code in my smaller data.
--
Thank for your help
BeSmart
"Mike Fogleman" wrote:
> Try this. It will loop through the cells in columns D thru BJ on whatever
> row Cell is equal to.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim Cell As Integer
> Dim Z As Integer
> Dim c As Range, RwRng As Range
>
> For Z = 17 To Cells(Rows.Count, "C").End(xlUp).Row
> For Cell = 43 To Cells(Rows.Count, "A").End(xlUp).Row
> If Cells(Cell, "A").Value Like "*" & Range("C" & Z).Value & "*" Then
>
> Set RwRng = Range(Cells(Cell, "D"), Cells(Cell, "BJ"))
> For Each c in RwRng
> If c.Value > "0" Then
> c.Interior.Color = Range("C" & Z).Interior.Color
> End If
> Next c
>
> End If
> Next Cell
> Next Z
> End Sub
>
> Mike F
> "BeSmart" <(E-Mail Removed)> wrote in message
> news:00CD887B-36CB-4F36-AAC7-(E-Mail Removed)...
> > Hi All
> >
> > Could someone help me - I need to change the following code to look at the
> > range of columns "D:"BJ" and apply the formatting - rather than just
> > looking
> > at column D? I've tried a few things and they haven't worked (I get error
> > msgs so I'm obviously doing it wrong)...
> >
> > Where "D" appears below, I need it to look & apply to a range of "D:"BJ".
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > Dim Cell As Integer
> > Dim Z As Integer
> >
> > For Z = 17 To Cells(Rows.Count, "C").End(xlUp).Row
> > For Cell = 43 To Cells(Rows.Count, "A").End(xlUp).Row
> > If Cells(Cell, "A").Value Like "*" & Range("C" & Z).Value & "*" Then
> > If Cells(Cell, "D").Value > "0" Then
> > Cells(Cell, "D").Interior.Color = Range("C" & Z).Interior.Color
> > End If
> > End If
> > Next Cell
> > Next Z
> > End Sub
> > --
> > Thank for your help in advance
> > BeSmart
>
>
> .
>
|
Similar Threads
