I'm trying to write a visual basic command to find all 3 digit numbers that have a value right of the hyphen greater than 50 and if so fill the cell blue. I have very little knowledge and need some help. Thanks in advance.
I'm trying to write a visual basic command to find all 3 digit numbers that have a value right of the hyphen greater than 50 and if so fill the cell blue. I have very little knowledge and need some help. Thanks in advance.
If you only want three digit numbers, then aren't you interested in numbers > 100? What you are asking is quite easy, but it's difficult to answer without seeing your workbook - can you upload it?
Thank you for the reply.
Example:
123-55
456-25
445566-22
4545-8
788-15
54454-10
I'm trying to have it identify only 3 digit numbers left of the hyphen that have a value greater than 50 right of the hyphen, if so fill color blue.
Ok, this should get you started:Please Login or Register to view this content.
Hello.
In what follows, I'll assume your data is from cell A1 down.
Instead of filling your active sheet with ugly colors, the attached VBA code gives you beautiful and funky symbols:
PHP Code:
Sub Macro8()
Dim a, Q&, i&, b
With ActiveSheet.[a1].CurrentRegion
a = .Value: Q = UBound(a): ReDim Preserve a(1 To Q, 1 To 2)
For i = 1 To Q
b = Split(a(i, 1), "-")
If Len(b(0)) = 3 And b(1) > 50 Then a(i, 2) = ChrW(9194)
Next
.Resize(, 2) = a
End With
End Sub
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
1) Conditional format.
Select range e.g. A1:A100
Formula:
=and(iferror(find("-",a1),0)<5,isnumber(left(a1,find("-",a1&"-")-1)+0),iferror(value(mid(a1,find("-",a1)+1,len(a1))),0)>=50)
2) vba
Please Login or Register to view this content.
Last edited by jindon; 01-31-2023 at 10:59 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks