If Else to change cell color

[carlomscalisi]

Please see the attached photo of the spreadsheet.

Here's what I'd like to happen:

When I click on the button "Add Row," I would like it to create a new row in row 17 between columns A and V (A17:V17). I need columns A-C (A17:C17) to be Merged and the text Left Aligned, and then the text in the rest of the columns D-V (D17:V17) to be Centered. I would like the text to be the same font and size, however I would like the text in V17 to be Bold. I would also like the format of cell D17 to be Text, so that "2/3" won't default to "3-Feb". I seem to have accomplished all of this so far with the code that I have:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("A17").Select
ActiveCell.EntireRow.Insert Shift:=x1Down
Range("A17:C17").Merge
Range("E17:V17").Orientation = xlHorizontal
Range("D17").NumberFormat = "@"
Range("O17:P17").Font.Size = "10"
Range("A17:V17").Interior.ColorIndex = 0

Sheets("Sheet1").Range("A17:V17").Select
Selection.Borders.Weight = xlThin
Selection.RowHeight = 12.75
Selection.Font.Bold = False
Range("A17:A17").HorizontalAlignment = xlLeft
Range("V17").Font.Bold = True

I am fully aware that, although this code works, it's probably not the best. However my problem is with the next thing that I would like to accomplish when hitting the Button:

I would like there to be If else with Conditional Formatting applied to A17:U17 wherein, if the text is N, the cell color will change to Red, and if it's anything else, the cell will have no fill. I have somehow pieced together the following code that I've applied directly below the above code:

Sheets("Sheet1").Range("A17:V17").Select
For Control = 1 To 1
Application.Wait (Now + TimeValue("0:00:1"))
For i = 1 To 100
For col = 5 To 21
If Cells(i, col).Value = "N" Then
Cells(i, col).Interior.ColorIndex = 3
Else
If Cells(i, col).Value = "Y" Then
Cells(i, col).Interior.ColorIndex = 0
End If
End If
Next
Next
Next
End Sub

Once again, I have no idea what some of these codes even do or mean, but I've copied and pasted from websites and tried to piece something together. What I've found is that it actually does change the cell color to Red if the text is "N", HOWEVER, it only changes the color if I hit the Button again and add another row.

So what I'd like to do, obviously, is to have the cell color change to Red automatically if "N" is entered in the cell. I don't know if there's a way to make it happen immediately, or if I need to do an Application.OnKey thing or something else.

I would GREATLY appreciate your help.

Thank you!

 

[Michael M]

How will the inserted row get populated, if the macro is running ?
IS there any option beside "Y" or "N" ??

Private Sub CommandButton1_Click()
With Sheets("Sheet1")
    .Range("A17").EntireRow.Insert
    .Range("A17:C17").Merge
    .Range("E17:V17").Orientation = xlHorizontal
    .Range("D17").NumberFormat = "@"
    .Range("O17:P17").Font.Size = "10"
    .Range("A17:V17").Interior.ColorIndex = 0
End With
With Range("A17:V17")
    .Borders.Weight = xlThin
    .RowHeight = 12.75
    .Font.Bold = False
End With
Range("A17:A17").HorizontalAlignment = xlLeft
Range("V17").Font.Bold = True
Sheets("Sheet1").Range("A17:V17").Select
'For Control = 1 To 1
'Application.Wait (Now + TimeValue("0:00:1"))
For i = 1 To 100
    For col = 5 To 21
        If Cells(i, col).Value = "N" Then
            Cells(i, col).Interior.ColorIndex = 3
        End If
        If Cells(i, col).Value = "Y" Then
            Cells(i, col).Interior.ColorIndex = 0
        End If
    Next col
Next i
End Sub

 

[carlomscalisi]

How will the inserted row get populated, if the macro is running ?
IS there any option beside "Y" or "N" ??

Well that demonstrates my extremely limited knowledge of macros and how they work. I guess I didn't really comprehend that the macro would run and then end immediately. I basically thought that if I put "N" in a cell, it would execute the command automatically. So that clearly explains the problem. The code works, but only if I run it (by pressing the Button), which is why it doesn't change the cell color until I run it again.

So, now that I understand. Is there a way to do what I want, namely, to change the cell color with the text is entered? Does it matter if the text is "N" or something else? I suppose it could be "No," but it would have to be something along those lines.

Please let me know. Thanks for your help!

 

[Michael M]

Reduce the original code to

Private Sub CommandButton1_Click()
With Sheets("Sheet1")
    .Range("A17").EntireRow.Insert
    .Range("A17:C17").Merge
    .Range("E17:V17").Orientation = xlHorizontal
    .Range("D17").NumberFormat = "@"
    .Range("O17:P17").Font.Size = "10"
    .Range("A17:V17").Interior.ColorIndex = 0
End With
With Range("A17:V17")
    .Borders.Weight = xlThin
    .RowHeight = 12.75
    .Font.Bold = False
End With
Range("A17:A17").HorizontalAlignment = xlLeft
Range("V17").Font.Bold = True
end sub

and put this one in the Sheet module

Sub Worksheet_change(ByVal target As Range)
If Cells(target.Row, target.Column).Value = "N" Then
    Cells(target.Row, target.Column).Interior.ColorIndex = 3
End If
End Sub

 

[carlomscalisi]

...

and put this one in the Sheet module

Sub Worksheet_change(ByVal target As Range)
If Cells(target.Row, target.Column).Value = "N" Then
    Cells(target.Row, target.Column).Interior.ColorIndex = 3
End If
End Sub

Hi Michael,

So how would I modify this code so that it only affects certain cells, namely E17:U17? I don't want all "N" in the sheet to be red, including at the end of the row in V17. Also, if I were to apply the code to only, say, E17:U17, and then later add another row and shift one down, would the cells with "N" still be red once they shift down and out of row 17?

Thank you for your help!