apllying color to cell which contain different starting number

D

desixcel

Urengt:
can anybody know how to apply colors to cells which start with a
different numbers.
FOr example: I m working on a sheet in which i have the cell s
containing different phone numbers starting with different series.
to make it clear:
i have phone numbers starting with the series 9848......etc......
and the other series starting with 08... and so on
how can i apply conditonal formating to these cells to have different
colors on my sheets.or is there any other method to higlight cells
starting with the diferent numbers or text.
 
F

Frank Kabel

Hi
not quite sure but maybe using the following formula in conditional
formating is what your looking for:
=LEFT(cell_reference,2)="98"
 
B

Bob Phillips

You will probably have more than 3 colour requirements, so conditional
formatting is lacking here. In this instance you will need VBA. Here is an
example

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("data")) Is Nothing Then
If Target.Count = 1 Then
Select Case Target.Value
Case 958: Target.Interior.ColorIndex = 1
Case 929: Target.Interior.ColorIndex = 3
Case 911: Target.Interior.ColorIndex = 5
Case 872: Target.Interior.ColorIndex = 16
Case Else: Target.Interior.ColorIndex =
xlColorIndexAutomatic
End Select
End If
End If
sub_exit:
Application.EnableEvents = True
End Sub

as this is worksheet event code, it is entered by right-clicking the sheet
tab, select View Code from the menu, and paste the code. It assumes that
your numbers are in a named range called 'data'.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top