That's great Pete, worked a treat (once I'd added a ')' that was missed off
each line). Do you know the code to change the font colour?
--
Marie Bayes


"Pete_UK" wrote:

Replace this part of your code:

Select Case UCase(rng.Value)
Case Is = "PM": Num = 22
Case Is = "BH": Num = 45
Case Is = "H": Num = 4
Case Is = "PE": Num = 35
Case Is = "E": Num = 5
Case Is = "R": Num = 6
Case Is = "P": Num = 29
Case Is = "A": Num = 7
Case Is = "PRE": Num = 8
End Select

with this:

IF(Left(UCase(rng.Value),2) = "PH" Then Num = 22
IF(Left(UCase(rng.Value),2) = "BH" Then Num = 45
IF(Left(UCase(rng.Value),1) = "H" Then Num = 4
IF(Left(UCase(rng.Value),2) = "PE" Then Num = 35
IF(Left(UCase(rng.Value),1) = "E" Then Num = 5
IF(Left(UCase(rng.Value),1) = "R" Then Num = 6
IF(Left(UCase(rng.Value),1) = "P" Then Num = 29
IF(Left(UCase(rng.Value),1) = "A" Then Num = 7
IF(Left(UCase(rng.Value),3) = "PRE" Then Num = 8

So that you do not need the Select Case or End Select statements or
anything in between.

Hope this helps.

Pete

On Dec 19, 4:04 pm, Marie Bayes
wrote:
Thanks Peter, I'm afraid I need a little more help than that, ie, I'm
applying this code to most of the spreadsheet, so what do I put in the 'text'
part of the LEN code, plus how do I then format the text colour in each cell?
--
Marie Bayes



"Pete_UK" wrote:
You'd normally use:

Left( text , no_of_characters )

to check "begins with" but in your case you have 1-, 2- and 3-
character strings, so this would be more complicated.

Pete

On Dec 19, 2:38 pm, Marie Bayes
wrote:
Hi

I've cribbed the following code from this site to use for some conditional
formatting. However, i'd like to change it so that if the cells BEGIN WITH
the letters I've put into the code the formatting will work. Plus I'd also
like to format the colour of the text using the same critieria, ie, if the
cell begins with "PM-" then colour the cell and colour the text. I'd be
grateful for any suggestions, thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A1:iv63000"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "PM": Num = 22
Case Is = "BH": Num = 45
Case Is = "H": Num = 4
Case Is = "PE": Num = 35
Case Is = "E": Num = 5
Case Is = "R": Num = 6
Case Is = "P": Num = 29
Case Is = "A": Num = 7
Case Is = "PRE": Num = 8
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

--
Marie Bayes- Hide quoted text -

- Show quoted text -