Highlight Cell if it contains a specific character in specific position.

[mykal66]

Hi folks - i need help again please but not sure if it is possible

I have a spreadsheet which has a sort code and account number in column B in this format 000000 08000000. There will always be a space between the 2 sets of numbers too

i need to be able to highlight any cell that has an 8 in the same position as above.

I've tried using wildcards in a formula as belwo but can't get it to work

=IF(B2="?????? ?8??????","Yes","No")

Can anyone help please?

Thank you

[CodeNinja]

well, if you know it will always be the 9th character in, its very easy to:

If Mid(Sheet4.Cells(1, 1), 9, 1) = 8 Then
Sheet4.Cells(1, 1).Interior.ColorIndex = 4
End If

Obviously, you will change sheet4.cells(1,1) to the cell you are checking...

[mykal66]

Thanks very much. Will make life much easier than manually looking down a huge column

[CodeNinja]

You could also do a conditional formatting with a formula like
=mid(A1,9,1)="8"
for the range you want...

[mykal66]

Hi again

This is much easier for me to show someone else how to do. Thank you again