Excel help - again

  Allan-263226 14:34 09 Dec 03
Locked

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 03

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 03

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 03

I see now...

  phil.smith 14:52 09 Dec 03

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

  Allan-263226 14:57 09 Dec 03

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

  pc moron 15:17 09 Dec 03

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

End Sub

  pc moron 15:19 09 Dec 03

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 03

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 03

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

  pc moron 16:15 09 Dec 03

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…