Adding new row and give color if cells are emty

Question

working on work project and i am stuck. I allready have a function thats add a new row over active cell.

Now i want to add grey color to new row, and when new row cells has letters or numbers in it, it will appear as no color (hvite). SEE IMAGE OF PROJECT HERE Also i dont want the color to go longer than column S as ilustrated in image.

Im not the author of this code. And theres is much i dont even understand. Code goes as follows. AND THERE MAY BE SOME TYPE ERRORS IN THIS CODE, HAD TO WRITE IT FROM A COMPUTER TO ANOTHER. THE CODE BELOW WORKS. just need to add the color to the row

`Sub insert_row()
  Dim LineNumber As Integer
  Dim insertionpoint
  Dim Rownumber, Positionrow As Integer
  Dim MarkedArea As String

 Application.ScreenUpdating = False 'Stops screenupdating
 Insertionpoint = ActiveCell.Address
 LineNumber = ActiveCell.Row

 For Rownumber = 5 To 1000
     If Range("B" & Rownumber).Value = "PLACE" Then
     Positionrow = Rownumber + 1
     End If
 Next Rownumber
 If LineNumber < Positionrow - 5 And LineNumber > 6 Then
     Range(Insertionpoint).Select
     Selection.EntireRow.Insert 'Inserts new row over active cell
     LineNumber = ActiveCell.Row
     Range("A" & LineNumber).Select
     ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",TEXT(RC[1],""DDMM"")&""0""&RC[2])"
     'More cell properties .....
     'More .....
     'More .....
      MarkedArea = "B" & LineNumber & ":X" & LineNumber
      Range("B" & LineNumber).Select

      'SetStandardFormat
      Range("AB6:AS6).Select ' not shown in picture
      Selection.Copy
      Range(Insertionpoint).Select
      Selection.PasteSpecial Paste:=x1PasteFormats, Operation:=x1None, _
           SkipBlanks:=False, Transpose:=False

 Else
      MsgBox ("Row can not be added here")
 End If
 Application.ScreenUpdating = False

 End Sub`

Also there is a button with this in it

Private Sub CommandButton2_Click()
'add row
Insert_row
End Sub

Hope for some help! Thanks.

Button simply executes **Insert_Row** Sub. What exactly is condition for coloring? Do you need all cells in a row to be empty? Can you attach example? — AntiDrondert, Oct 23 '17 at 12:38
I want the whole row to be colored. So it doesnt matter wich cell is text in it for it to change. I have uploaded one image. — Joakim Grønstrand, Oct 23 '17 at 13:01

Cyril · Accepted Answer · 2017-10-24T13:21:25.597

0

You just want a grey-color to the added row?

Insertionpoint = ActiveCell.Address
Range(Insertionpoint).Select
Selection.EntireRow.Insert
With Range(Insertionpoint).EntireRow.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With

Giving what I used to test... didn't fix any .select, and pulled out what I needed to test, from your code.

Edit

Adding some code for the loop to add color... will assume that the date is in Column B:

Dim i As Long, LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row 'assumes column A is contiguous
For i = 2 To LR 'Assumes row 1 is headers
    If Cells(i, "B").Value = "" Then
        With Rows(i).EntireRow.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
        End With
    Else
        Rows(i).EntireRow.Interior.Color = xlNone
    End If
Next i

edited Oct 24 '17 at 13:21

answered Oct 23 '17 at 13:22

Cyril

6,448
1
18
31

my row got the color grey with this code. but there was a few cells below that row that also changed color. maby my code have some issues. Is there any other way to make a button add a row with special formatting option? And also, your code just adds grey, i want to add grey IF one or more cells are empty. Thanks – Joakim Grønstrand Oct 24 '17 at 08:19
@JoakimGrønstrand I might sound too obvious, but aren't all cells of added row empty? – AntiDrondert Oct 24 '17 at 08:40
Yes thats correct. The only reason that i want empty cells to be colored is because i want some rows to be emtpy to seperate days from each other. Example. Monday (hvite row) Seperator (Grey) Tuesday (hvite) Tuesday (hvite) Seperator (Grey) – Joakim Grønstrand Oct 24 '17 at 09:18
But i think ill just use the above code for a different button called separator or something. think that will work fine. will test that out. – Joakim Grønstrand Oct 24 '17 at 09:19
@JoakimGrønstrand The issue that AntiDrondert brought up would still stand, when trying to separate those items. Each new row added will be empty, so rather than using an If-statement to tell if it is empty, I just made it grey. If you want to have a button that checks through all lines for empty cells, then you would want a loop to go through the sheet. – Cyril Oct 24 '17 at 13:17
@JoakimGrønstrand See edit. Also gave the option that if the cells are updated, it will do the Else fill is nothing (blank). – Cyril Oct 24 '17 at 13:22
@Cyril Thanks for answer, didnt get it to work. When i inserted your code and run my Insert row function everything got grey. Anyway, i have updated my post, if you have time, please read it again. i have a feeling there has been some confusion. There is also a new image for better explaining. Thanks – Joakim Grønstrand Oct 25 '17 at 06:52
@JoakimGrønstrand Did you make any adjustments? Assumptions were made, and I commented them into the code, as well as prefaced with an assumption about where the dates are in your spreadsheet. Without changing anything, I will explain a bit further: the first bit of code would be put into your current code after the row is added; the row WILL BE BLANK so it will immediately be colored grey. The second bit of code can be run at ANY point and will color rows grey which have blanks in COLUMN B (assumption, you will need to verify/change). Hopefully that makes more sense? – Cyril Oct 25 '17 at 13:11
Thanks tested aigain. It works. But theres are a few things. I have added new row, its gray, i insert some text in column B and press enter. nothing happens. not until i add a new row then it updates. Also, now my whole worksheet is grey (cause theres no text in B) Not sure how to solve that, but this function should only be happening below row 6 to the last added row. also not all the way to the right. – Joakim Grønstrand Oct 26 '17 at 09:48
@JoakimGrønstrand In the short term to remove the color from the sheet, you can use Cell.Interior.Color = xlNone (just make it its own sub) to remove all interior colors from the active sheet. I believe you might want to look into conditional formatting, rather than just coloring the rows. This would allow Excel to be more dynamic, so when things do get filled in, the color is gone immediately, rather than having to run a macro to update. See: https://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code . Conditional formatting can be through Excel or VBA. – Cyril Oct 26 '17 at 12:35

Adding new row and give color if cells are emty

1 Answers1