Sorry about the missing bracket - I realised after I had posted.

At the bottom of your code you have:

rng.Interior.ColorIndex = Num

which sets the background colour. You will need to add this statement
below it:

rng.Font.ColorIndex = Num2

to set the foreground colour. However, you will need to define what
Num2 is, so at the top of your code add this line:

Dim Num2 As Long

and then for each of those statements that I gave you you will have to
specify what value you want for Num2, like this:

IF(Left(UCase(rng.Value),2) = "PH") Then Num = 22 : Num2 = 4

for the first one. (Be wary of word wrap when you view this post -
this should be all one line).

You will need to specify an appropriate colour for Num2 for each of
those if statements.

Hope this helps.

Pete

On Dec 19, 4:50*pm, Marie Bayes
wrote:
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 -- Hide quoted text -

- Show quoted text -