change

If idx < 1 Or idx 56 Then idx = xlAutomatic

to
If idx < 1 Or idx 56 Then idx = xlNone

Peter T

"Peter T" <peter_t@discussions wrote in message
...
Try following in the worksheet module (right-click sheet tab view code)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim idx As Long

On Error GoTo errH
If Right$(Target(1), 2) = "##" Then
idx = Val(Target(1))
If idx < 1 Or idx 56 Then idx = xlAutomatic
Application.EnableEvents = False
Target(1).Interior.ColorIndex = idx

Target(1).ClearContents
End If

errH:
Application.EnableEvents = True
End Sub

Record a macro while formating cells with your favourite colours to get
colorindex's.

Type in your colorindex followed by "##". To clear colour format simply
type
"##". (Obviously you could amend "##")

If you want this to work on all sheets, put similar code in the
"ThisWorkbook" module but you need to change the event as follows -

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

'code

End Sub

Regards,
Peter T

"haifa" wrote in message
...
Does anyone know how I can shade certain cells in by typing in a letter
or
number, using If statements?