Excel conditional formatting

[sarahgeorge]

Hi i posted on allexperts.com to Anne, and got a great reply see below, but now need a little coding to help me (see below)

Code given
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error Goto 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "Tom", "Joe", "Paul"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "Smith", "Jones"
Cell.Interior.ColorIndex = 4
Cell.Font.Bold = True
Case 1, 3, 7, 9
Cell.Interior.ColorIndex = 5
Cell.Font.Bold = True
Case 10 To 25
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Case 26 To 99
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

How do i modify the code

1. I only want the formatting to work on a range of cells ie. B190 not the whole sheet, how can i adapt the code to do this? Is it this line?...
Set Rng1 =ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)

2. I want the background to change color when i enter
"P - 08/06/07" into a cell- 08/06/07 being any date that can change

Really appreciate help on this
Keep up the good work

Sarah

[Bob Phillips]

So why didn't you post this back there?

1,

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range

On Error GoTo ws_exit
If Not intesrect(Target, Me.Range("B1:B90")) Is Nothing Then
With Target
Select Case .Value
Case vbNullString
.Interior.ColorIndex = xlNone
.Font.Bold = False
Case "Tom", "Joe", "Paul"
.Interior.ColorIndex = 3
.Font.Bold = True
Case "Smith", "Jones"
.Interior.ColorIndex = 4
.Font.Bold = True
Case 1, 3, 7, 9
.Interior.ColorIndex = 5
.Font.Bold = True
Case 10 To 25
.Interior.ColorIndex = 6
.Font.Bold = True
Case 26 To 99
.Interior.ColorIndex = 7
.Font.Bold = True
Case Else
.Interior.ColorIndex = xlNone
.Font.Bold = False
End Select
End With
End If
[/vba]

2. As well as the others, or just that condition?

[unmarkedhelicopter]

Surely the intersect should equal target ???

[sarahgeorge]

Thank you for your relpy.

What is the formula i need to write to also in the same piece of code change the background cell colour if the cell contains the text "P1 - 02/04/07" Where 02/04/07 can be any date not just the 2nd April 2007?

When i ran the code above it sayed sub or function not defined and highlighted the following lines of code:

Private Sub Worksheet_Change(ByVal Target As Range)

and also the word intersect from the following line:

If Not intesrect(Target, Me.Range("L3:X90")) Is Nothing Then

Please Help me!
Kind regards
Sarah

[Bob Phillips]

I have done it to colour it red, you didn't say which colour

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("L3:X90")) Is Nothing Then
With Target
If Left(.Value, 5) = "P1 - " Then
If IsDate(Right(.Value, Len(.Value) - 5)) Then
.Interior.ColorIndex = 3
.Font.Bold = True
End If
Else
Select Case .Value
Case vbNullString
.Interior.ColorIndex = xlNone
.Font.Bold = False
Case "Tom", "Joe", "Paul"
.Interior.ColorIndex = 3
.Font.Bold = True
Case "Smith", "Jones"
.Interior.ColorIndex = 4
.Font.Bold = True
Case 1, 3, 7, 9
.Interior.ColorIndex = 5
.Font.Bold = True
Case 10 To 25
.Interior.ColorIndex = 6
.Font.Bold = True
Case 26 To 99
.Interior.ColorIndex = 7
.Font.Bold = True
Case Else
.Interior.ColorIndex = xlNone
.Font.Bold = False
End Select
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[unmarkedhelicopter]

Bob ???