• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

change background color of new row

mdavid

Member
Hi,
Have the following code:
Code:
Private Sub CommandButton1_Click()
Dim r As Integer
Dim c As Integer
Dim rng As Range
r = ActiveCell.Row + 1
Rows(r).Insert shift:=xlShiftDown
ActiveSheet.Cells(r, 1).Value = vbCrLf & "New " & vbCrLf & "Row" & vbCrLf
Set rng = Range(Cells(r, 2), Cells(r, 15))
For Each cell In rng
   cell.Interior.Color = vbYellow
Next cell
Unload Me
UserForm8.Show
End Sub

Above code works without error, the only thing it doesn't do is change background color of cells B - P on row r to vbYellow, Why?

Thanks for your help
David
 
Code:
Private Sub CommandButton1_Click()
Dim r As Integer
r = ActiveCell.Row + 1
Rows(r).Insert shift:=xlShiftDown
ActiveSheet.Cells(r, 1).Value = vbCrLf & "New " & vbCrLf & "Row" & vbCrLf
Range(Cells(r, 2), Cells(r, 15)).Interior.Color = vbYellow
Unload Me
UserForm8.Show
End Sub
 
Hi Hui, Thanks for this.
Still has no effect on the background color of the cells.
I have Conditional Formatting rules that determine background color of the row, and the row receives the color of the row above, but the column value used in the rule (If IsOdd/IsEven) to decide the color is Null after "Rows(r).Insert shift:=xlShiftDown". How would I over-ride the formatting rules for this row?
 
Hi Hui,
Got it sorted, replaced Null with "A" in the formatting rule column.
Thanks for your help
David
 
CF, override cells colors and so need to be compatible with your requirements
 
Private Sub Workbook_Open()
For Each cell In Worksheets("sheet1").Range(Worksheets("sheet1").Range("A1"), _
Worksheets("sheet1").Range("A1").End(xlDown))
If cell.Font.Color = RGB(255, 0, 0) Then
cell.Font.Color = RGB(0, 0, 0)
cell.EntireRow.Font.Bold = True
cell.EntireRow.Interior.Color = RGB(255, 0, 0)
End If

Next cell
End Sub


site: Re-Reality
 
Back
Top