I'm currently working on a spreadsheet with various different macros in it. The current macro I'm having problems with is a Private Sub on a Worksheet which is called on any Change on the worksheet.
What I am trying to achieve is depending on the value in the column named "Status" the entire row will have its background changed to either white or blue. There are a couple of columns I want to leave black as they are code generated cells so I've split this into 3 blocks of code. One hiccup I had with this was that the grid lines on the line effected vanished so I've had to add in code to actually put in proper grid lines rather than the default grey ones that you get on a blank sheet.
All is good at this point with the code using lettered references for the columns A, B, C, etc. but I'm trying to use defined column names throughout the code so that if a user adds a column into the spreadsheet at any point the macros will still run correctly and I can't seem to get this working for this section of code.
Below is the section of code I've managed to get working:
Select Case Range("Status").Cells(Target.Row, 1)
Case "Required"
Range("A" & Target.Row & ":S" & Target.Row).Interior.ColorIndex = 2
Range("U" & Target.Row & ":V" & Target.Row).Interior.ColorIndex = 2
Range("X" & Target.Row & ":Z" & Target.Row).Interior.ColorIndex = 2
Sheet4.Range("A" & Target.Row & ":AB" & Target.Row).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Case "Deleted"
Range("A" & Target.Row & ":S" & Target.Row).Interior.ColorIndex = 42
Range("U" & Target.Row & ":V" & Target.Row).Interior.ColorIndex = 42
Range("X" & Target.Row & ":Z" & Target.Row).Interior.ColorIndex = 42
Sheet4.Range("A" & Target.Row & ":AB" & Target.Row).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Select
Display More
What I'm trying to do is change the first Range lines to reference my defined columns names along the lines of:
The other headache I've got with this code is that if the cell "Status", Target.Row is changed by another sheet within the workbook (There are two sheets, one with basic information and another with more in depth information and common columns on both sheets that are copied onto the other sheet so that both sheets have the correct information) then I start getting "Run-Time error 1004: Select method of Range class failed" messages.
Any help with this would be much appreciated!