Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event for multiple rows
I have a spreadsheet with 100's of rows. As dates are changed on each row I
would like a specific range in that row to change colors accordingly. How do I make a specific column range a variable, so that if a cell changes in that column, it then makes that row change colors of the cell changed. Here is my code for a specific row. Private Sub Worksheet_Change(ByVal Target As Range) mycell = Range("m7") If IsDate(mycell) Then Range("A7:P7").Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Else Range("A7:P7").Select With Selection.Interior .ColorIndex = 34 .Pattern = xlSolid End With End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event for multiple rows
Thanks,
If you want this to work only one cell at a time: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < Range("m7").Column Then Exit Sub If Target.Cells.Count 1 Then Exit Sub With Cells(Target.Row, 1).Resize(1, 16).Interior If IsDate(Target) Then .ColorIndex = 15 .Pattern = xlSolid Else .ColorIndex = 34 .Pattern = xlSolid End If End With End Sub For multiple cells, you would need to loop or check the date of Target.Cells(1) HTH, Bernie MS Excel MVP "Thanks" wrote in message ... I have a spreadsheet with 100's of rows. As dates are changed on each row I would like a specific range in that row to change colors accordingly. How do I make a specific column range a variable, so that if a cell changes in that column, it then makes that row change colors of the cell changed. Here is my code for a specific row. Private Sub Worksheet_Change(ByVal Target As Range) mycell = Range("m7") If IsDate(mycell) Then Range("A7:P7").Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Else Range("A7:P7").Select With Selection.Interior .ColorIndex = 34 .Pattern = xlSolid End With End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change event for multiple cells | Excel Programming | |||
VB multiple criteria countif change event | Excel Programming | |||
Inserting Rows with the change event active | Excel Programming | |||
Can't hide/unhide rows under worksheet change event? | Excel Programming | |||
Change event for multiple checkboxes | Excel Programming |