If cells starts with a number and exceeds a certain number of characters

[CJW_14]

Hi All,

I'm trying to write a macro to highlight a cell IF

- the cell starts with a number and the number of characters in the cell exceeds 8

For example, I would want the followings values in red to be highlighted

• 10000011
• 100212549
• 10020022, 10236598
• 10856985 & 56987569
• 12659875 73965413
• 12665485 - 85698755
• Orange
• Red Black Red
• 102554 Red
• Orange 10658

Any help would be much appreciated

[MagPower]

Try this:

Option Explicit

Sub cjw_14

Dim rng as range, c as range

Set rng = Selection
For each c in rng
  If Len(c.value) > 8 and IsNumeric(Left(c.value, 2)) Then
     c.Font.Color = vbRed
  End If
Next c
End Sub

[CJW_14]

Thanks alot MagPower!, works great

[MagPower]

Happy to help!

-- Russ

[rothstein]

Try this:

Option Explicit

Sub cjw_14

Dim rng as range, c as range

Set rng = Selection
For each c in rng
  If Len(c.value) > 8 and IsNumeric(Left(c.value, 2)) Then
     c.Font.Color = vbRed
  End If
Next c
End Sub

Just out of curiosity, why the left 2 characters and not just the first character on the left as digits (that seemed to be all the OP was restricting it to)?

Another, simpler way to write your If..Then statement but requiring only the left character to be a digit (which is all I think the OP was restricting the value to)...

If c.Value Like "#????????*" Then

Change the quoted pattern to "##???????*" if there is a reason the left two characters must both be digits of this test.

[MagPower]

@Rick - you are correct, it should only be the first char, not the first two as per OP request. I was tired that day ��

Russ

[Paul_Hossler]

I'd add a safety check to make sure a Range was selected, and a way to 'un-red' any cells that were updated and didn't meet the criteria anymore

Option Explicit

Sub phh_1()
    Dim c As Range

    If Not TypeOf Selection Is Range Then Exit Sub
    For Each c In Selection.Cells
         c.Font.ColorIndex = IIf(c.Value Like "#????????*", 3, xlColorIndexNone)    '   # 8?s *
    Next c

End Sub

[rothstein]

I'd add a safety check to make sure a Range was selected, and a way to 'un-red' any cells that were updated and didn't meet the criteria anymore

Option Explicit

Sub phh_1()
    Dim c As Range

    If Not TypeOf Selection Is Range Then Exit Sub
    For Each c In Selection.Cells
         c.Font.ColorIndex = IIf(c.Value Like "#????????*", 3, xlColorIndexNone)    '   # 8?s *
    Next c

End Sub

Do you real want the non-red font color to be xlColorIndexNone? I would think it should be 1 (black)?

[Paul_Hossler]

Probably should be xlColorIndexAutomatic

My test case wasn't as robust as it should have been

Capture.JPG