Format numbers depending on case of letters (excel xp)

Topic

New Reply

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

Reply | Quote

Replies

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

Steve

Reply | Quote

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.

Reply | Quote