Hi guys,I am creating a shift rota, and in all of the blank cells I would like them to show as N.For example:E = Early Shift, L = Late shiftI want all of the fields I have not filled with these two letters to show as N. Can I use a formula for this?
You want a forumla in a cell that will show an N if its empty?Even if you could do that you'd have to paste the forumla in so why not just paste in the "N"??
Hi Jester2K II,I have fields from B2:BS16 filled in, I only want these to change to N when they are blank.The rota is often being changed around, therefore it would save me a lot of time if the formula, macro, etc.. did this for me
I see now...
Try going to FORMAT, CONDITIONAL FORMATTING(click on ? for assistance)
Wouldn't that just help me with conditional formatting? I need a formula!!
Open the VBA Editor(Alt+F11), find the worksheet you're working on and paste the following into the code window for that sheet.Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Dim c As Range Set Target = Range("B2:BS16") For Each c In TargetIf c.Value = "" Then c.Value = "N"Next End Sub
I'll never get the hang of posting here.If you don't understand the above, post back and I'll explain.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)With ActiveCellif .Row > 16 Or .Column = 1 Or .Column > 70 Then Exit SubEnd WithIf ActiveCell.Value = "" Then ActiveCell.Value = "N"End Sub
I keep getting a syntax error with the last VB script, the first one didn't do anything.
Have you pasted the script into the Sheet part in the VBA Editor?Where does the error occur?
This thread is now locked and can not be replied to.
iMac Pro review
Illustrator Charles Williams on how to create magazines and book covers
Les meilleures prises CPL (2018)
© Copyright 2018 IDG UK. All Rights Reserved.