finding first 2 digits in a cell from a row and mark them orange

**Megatronixs** · 05-24-2013, 04:32 AM

Hi all,

I have excel sheet where I have in a row some serian numbers. When the serial starts with 99, I need to mark this cell with orange. I just don't know how to pull this one. The numbers are in column E and the lengt of the column changes from time to time.

Any help is more then welcome.

Greetings.

**:) Sixthsense :)** · 05-24-2013, 05:29 AM

Try this code...

Sub ColorCellsBasedOnConditon()
Dim nEndRw As Long, i As Long

nEndRw = Cells(Rows.Count, "E").End(xlUp).Row

Application.ScreenUpdating = False

For i = 1 To nEndRw
   With Cells(i, "E")
      If Trim(.Value) = "99" Then .Interior.ColorIndex = 40
   End With
Next i

Application.ScreenUpdating = True

End Sub

**JasperD** · 05-24-2013, 05:38 AM

Sixthsense : your solution seems to look if the entire value in the cell is "99" and then mark yellow. Megatronixs wants to know if the serial number starts with 99...
I think the code below does what he's looking for.

Sub test()
For Each cell In Range("E1:E" & Range("E65536").End(xlUp).Row)
If Left(Trim(cell.Value), 2) = "99" Then cell.Interior.ColorIndex = 40
Next cell
End Sub

**Megatronixs** · 05-24-2013, 05:36 AM

Hi,
It works, but only if the cell has only 99 in it, if it starts with 998887, it does not find it. I need to select when a cell has a number that starts with 99........

Thanks for you help, I really appriciate it ;-)

Greetings

**:) Sixthsense :)** · 05-24-2013, 05:40 AM

Oops oversight error...

replace this line

If Trim(.Value) = "99" Then .Interior.ColorIndex = 40

With

If Left(Trim(.Value), 2) = "99" Then .Interior.ColorIndex = 40

**etaf** · 05-24-2013, 05:37 AM

can you just fill the cell colour or are you looking to mark the characters , I dont think that can be done

the fill for the cell would be conditional formatting

=Left(cell,2)=99
and colour orange

now in case you alos have text , I have used
=VALUE(LEFT(A1,2))=99

see attached

**Megatronixs** · 05-24-2013, 05:43 AM

Hi all,

Thanks all for the fast help. JasperD is right, I needed to find a cell that starts with 99. So the solution from JasperD works perfect for my need.
I just had no clue that the macro could be so simple (if you know the code). I'm still learning VBA and I must say that it still looks like Chinese to me ;-)

Thank you all for your solutions.

Greetings.