Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check for total digit count (2000 sp3)

    Greetings,
    Is it possible to check a cell for the size of the number? Example. cell A1 contains a number that is 9 digits. If it is over 9 digits, can that be check for, and change the cell color?

    Thanks,
    Brad

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Check for total digit count (2000 sp3)

    you could use conditional formatting to set the format to something when the number is greater than or equal 1000000000

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for total digit count (2000 sp3)

    Select the cell or cells you want to check. On the Format menu select "Conditional Formatting". In the dialog box, change the dropdown list to "Formula is". In the formula box enter the formula:

    <code>
    =LEN(A1)>9
    </code>

    Change the A1 cell address to the address of whatever the active cell is.

    Click the format button and select whatever formatting you want.
    Click OK to close all dialog boxes.
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for total digit count (2000 sp3)

    Legare,
    Sorry to say that for the specific column, I have used all three conditional formats.


    Brad

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check for total digit count (2000 sp3)

    It always helps if you provide relevant information at the outset. You can also use the worksheet's Change event.
    Right-click the worksheet tab and select View Code from the popup menu.
    Type or copy/paste the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("A:A")).Cells
    If IsNumeric(oCell) Then
    If Abs(oCell) >= 10^9 Then
    oCell.Interior.ColorIndex = 6
    Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End If
    Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for total digit count (2000 sp3)

    Hans,
    I added the code as you suggested, however it does not appear to be working. For that worksheet, I already have some code populated. How might I get this code to work along with the existing code?

    Thanks,
    Brad


    Current code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, Range("C:E")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("C:E"))
    If UCase(oCell.Value) = "NA" Or UCase(oCell.Value) = "N/A" Then
    oCell.Value = "N/A"
    End If
    Next oCell
    Application.EnableEvents = True
    End Sub

    Your Code

    Private Sub Worksheet_Change (ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("c:c")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("c:c")).Cells
    If IsNumeric(oCell) Then
    If Abs(oCell) >= 10 ^ 9 Then
    oCell.Interior.ColorIndex = 6
    Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End If
    Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check for total digit count (2000 sp3)

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("c:c")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("C:C")).Cells
    If IsNumeric(oCell) Then
    If Abs(oCell) >= 10 ^ 9 Then
    oCell.Interior.ColorIndex = 6
    Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End If
    Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("C:E")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("C:E"))
    If UCase(oCell.Value) = "NA" Or UCase(oCell.Value) = "N/A" Then
    oCell.Value = "N/A"
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for total digit count (2000 sp3)

    Hans,
    Works Great!

    Thanks,
    Brad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •