PDA

View Full Version : Highlight Cell if it contains a specific character in specific position.



mykal66
06-26-2012, 08:45 AM
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
06-26-2012, 09:24 AM
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
06-26-2012, 09:45 AM
Thanks very much. Will make life much easier than manually looking down a huge column

CodeNinja
06-26-2012, 10:03 AM
You could also do a conditional formatting with a formula like
=mid(A1,9,1)="8"
for the range you want...

mykal66
06-26-2012, 11:51 AM
Hi again

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