VBA Highlight Split Values in a Cell < 10

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
1,168
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I am looking for some VBA code that will highlight any value (Ignoring blanks) that has a value <10

20/24/20/8
32/32/18
24/23/24
25/26/25
11
26/26/25
26/25/28
22/10/22
21/9/9/10
13
21/17/18
16/17/6/17
21/22/21
24/24/2/24
22/23/23
24/26/23
23/25/24
24/26/26
15/4/11
7
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Please explain your logic for the highlighted items. I clearly am not understanding what you are showing. Mind reading is not in my skill bank.
 
Upvote 0
How about
VBA Code:
Sub StephenIV()
   Dim Cl As Range
   Dim Sp As Variant
   Dim i As Long
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Sp = Split(Cl.Value, "/")
      For i = 0 To UBound(Sp)
         If Val(Sp(i)) < 10 Then
            Cl.Font.Color = vbRed
            Exit For
         End If
      Next i
   Next Cl
End Sub
 
Upvote 0
try this code, which colors them yellow adjust to whatever formatting you require
VBA Code:
Sub test()
Dim txt As String

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))

For i = 1 To lastrow
txt = inarr(i, 1)
txtArr = Split(txt, "/")
    For j = 0 To UBound(txtArr)
        If txtArr(j) < 10 And txtArr(j) > 0 Then
          With Range(Cells(i, 1), Cells(i, 1)).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
          End With
          Exit For
        End If
    Next j
Next i

End Sub
 
Upvote 0
Ahhh!! Beautiful! Thank you very much Fluff and offthelip! I am so thankful and grateful for the help! Have a great day!!!!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Here is another, shorter macro that will also work...
VBA Code:
Sub HighlightIfCellContainsLessThan10()
  Dim Cell As Range
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    If "/" & Cell.Text & "/" Like "*/#/*" Then Cell.Interior.Color = vbYellow
  Next
End Sub
 
Upvote 0
VBA Code:
Sub HighlightIfCellContainsLessThan10()
Dim Cell As Range
For each Cell In Range("A1",Cells(Rows.Count,"A".End(xlUp))
   If "/" & Cell.Text & "/" Like "*/#/*" Then Cell.Interior.Color = vbYellow
Next
End Sub

Mr. Rothstein.

I copied and pasted the OP's sample data into a worksheet and then did the same to your code. When I first read your code, I expected to see any cell with the sequence of: Any characters then a slash, then a number, then another slash then any characters. I expected this from looking at your 'IF' statement though I'll admit. I hadn't figured out what the first part of that statement (before the word 'LIKE') meant. I was shocked by the result after running your code because it was nothing even close to what I'd expected.

1) I know the asterisk means 'any character' at the beginning and end, then I'd have thought any cell containing a slash, number, then slash between the asterisks would have been highlighted, but most weren't. Why not? It was only the cells that contained three slashes that were highlighted.
2) Where in your code does it say that numbers less than 10 should be highlighted?

There has to be something in that first part of your 'IF' statement that I'm missing. I'd sure appreciate any help you may offer in helping me to understand what you wrote. Thank you.

TotallyConfused (boy, am I ever now) :)
 
Upvote 0
The OP has not provided any comment for the code I posted letting us know if it worked for him or not, so I am not sure if he has even seen it yet. However, to answer your questions...

1) As I understand it, the OP wants to identify any cell where a value less than 10 appears either by itself in the cell or delimited by slashes within the text in the cell.

2) All single digit numbers are less than 10, so I simply test for a single digit (the single # sign in my pattern) surrounded by slashes... the asterisks in the pattern mean the single digit surrounded by slashes can appear anywhere in the text.

The part of my code before the Like operator surround the cell text with slashes and tests that against the pattern. The reason for doing this is so the pattern will be able to find single digits at the beginning or the end of the text should they appear at either of those locations in addition their appearing inside the text.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top