Shading cells with If statements

[haifa]

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

[Peter T]

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" <haifa@discussions.microsoft.com> wrote in message
news:4EAF000F-3713-41F8-A050-CC19866FF0BA@microsoft.com...
> Does anyone know how I can shade certain cells in by typing in a letter or
> number, using If statements?

[Peter T]

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
news:uPKWBHlKGHA.2808@TK2MSFTNGP15.phx.gbl...
> 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" <haifa@discussions.microsoft.com> wrote in message
> news:4EAF000F-3713-41F8-A050-CC19866FF0BA@microsoft.com...
> > Does anyone know how I can shade certain cells in by typing in a letter
or
> > number, using If statements?
>
>

[Arnold Reedy]

Why don't you just use the
* Menu - Format - Conditional Formatting and
* set "Cell value is" "equal" "A"
* Hit the format button and choose whatever text/cell formatting you want

"haifa" wrote:

> Does anyone know how I can shade certain cells in by typing in a letter or
> number, using If statements?