Conditional Format VB Code for Begins With

[Marie Bayes]

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

 

[Pete_UK]

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

 

[Marie Bayes]

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?

 

[Pete_UK]

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

 

[Marie Bayes]

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?

 

[Pete_UK]

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