Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select range in column, calculate on range plus 3 columns, output inrange plus 7 columns | Excel Programming | |||
Create two columns from a single column with (first and last name) | Excel Discussion (Misc queries) | |||
Single column into multiple columns | Excel Discussion (Misc queries) | |||
Copy column range of "single word" cells with spaces to a single c | Excel Discussion (Misc queries) | |||
Change the width of a single column in a column chart | Charts and Charting in Excel |