Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Format numbers depending on case of letters (excel xp)

    I have a list of numbers in COL b and letters in COL C. This macro will make the numbers in B bold or italic depending on the case of the letter that may exist next to it in COL C. Before, one one letter could exist in COL C but now there could be multiple letter like BbC. If there exists a Cap and a lower case letter the number to the left needs to be in bold and italic (bold italic). I don't know how to program the macro to look in the cell and if there is an upper and a lowercase letter to make the cell to the left bold italic. Thank you for the help.

    endcol = InputBox("How many columns are there?")
    Application.ScreenUpdating = False

    For j = 1 To endcol Step 3
    For i = 0 To lLastRow
    With ActiveSheet.Range("A1")
    If .Offset(i, j + 1).Value = "" Then
    .Offset(i, j).Font.Bold = False
    .Offset(i, j).Font.Italic = False
    Else
    If .Offset(i, j + 1).Value = UCase(.Offset(i, j + 1).Value) Then
    .Offset(i, j).Font.Bold = True
    .Offset(i, j).Font.Italic = False
    Else
    .Offset(i, j).Font.Bold = False
    .Offset(i, j).Font.Italic = True
    End If
    End If
    End With
    Next i
    Next j

  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: Format numbers depending on case of letters (excel xp)

    <pre>If .Offset(i, j + 1).Value = "" Then
    'cell is blank
    'set Bold/Italics as desired

    elseif isnumeric(.Offset(i, j + 1).Value) Then
    'cell is a number
    'set Bold/Italics as desired

    elseIf .Offset(i, j + 1).Value = UCase(.Offset(i, j + 1).Value) Then
    'all letters are upper case
    'set Bold/Italics as desired

    elseIf .Offset(i, j + 1).Value = LCase(.Offset(i, j + 1).Value) Then
    'all letters are lower case
    'set Bold/Italics as desired

    Else
    'letters are both upper/lower case
    'set Bold/Italics as desired

    End if
    </pre>


    Steve

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format numbers depending on case of letters (excel xp)

    Have you thought about looping through each letter in the cell and finding the ASCII character code for the letter? Look for the ASC function in VBA help.

    Upper case letters will (should) have values between 65 and 90, while the lowercase letters will be in the range of 97-122.

Posting Permissions

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