PDA

View Full Version : If cells starts with a number and exceeds a certain number of characters



CJW_14
03-15-2019, 10:24 PM
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
03-16-2019, 09:47 AM
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
03-16-2019, 02:23 PM
Thanks alot MagPower!, works great

MagPower
03-16-2019, 02:52 PM
Happy to help!

-- Russ

rothstein
03-24-2019, 08:04 AM
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
03-24-2019, 08:42 AM
@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
03-24-2019, 09:09 AM
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
03-24-2019, 09:22 AM
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
03-24-2019, 12:16 PM
Probably should be xlColorIndexAutomatic

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

23945