Change code to look at a range of columns not a single column
 

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

Change code to look at a range of columns not a single column
 

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" wrote in message
...
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

Change code to look at a range of columns not a single column
 

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" wrote in message
...
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


.