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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.