Excel help - again

  Allan-263226 14:34 09 Dec 2003

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 shift

I want all of the fields I have not filled with these two letters to show as N. Can I use a formula for this?

  Jester2K II 14:38 09 Dec 2003

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"??

  Allan-263226 14:47 09 Dec 2003

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

  Jester2K II 14:51 09 Dec 2003

I see now...

  phil.smith 14:52 09 Dec 2003

Try going to FORMAT, CONDITIONAL FORMATTING(click on ? for assistance)

  Allan-263226 14:57 09 Dec 2003

Wouldn't that just help me with conditional formatting? I need a formula!!

  pc moron 15:17 09 Dec 2003

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 Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim c As Range

Set Target = Range("B2:BS16")

For Each c In Target
If c.Value = "" Then c.Value = "N"

End Sub

  pc moron 15:19 09 Dec 2003

I'll never get the hang of posting here.

If you don't understand the above, post back and I'll explain.

  pc moron 15:44 09 Dec 2003

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With ActiveCell
if .Row > 16 Or .Column = 1 Or .Column > 70 Then Exit Sub
End With

If ActiveCell.Value = "" Then ActiveCell.Value = "N"

End Sub

  Allan-263226 16:10 09 Dec 2003

I keep getting a syntax error with the last VB script, the first one didn't do anything.

  pc moron 16:15 09 Dec 2003

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.

Elsewhere on IDG sites

Moto G6 Play Review: First Look

iPad 9.7in (2018) review

Comment utiliser Live Photos ?