VBA Help - Search range for cells with text and Align text to the Right and Increase Font Size

[Johnny Thunder]

Hello all,

working on a code that needs to be able to search a specific range Sheet1.Range("N15:EM134") Once found process the below actions:

• Align text to the right of the cell
• Increase the font size of the found text to size 15

Alternatively, I would need to do the same thing in the same range but for cells with numbers only:

• Align text to the center
• Decrease font size to 11
• *One exception, there will be cells with only numeric values that contain a dash (example: "1-5")

Any help on this would be greatly appreciated. This was the one piece of the puzzle that I am not really knowledgeable on doing.

 

[Micron]

a code that needs to be able to search a specific range Sheet1.Range("N15:EM134") Once found

Once "what" is found?

 

[Johnny Thunder]

Oh darn, sorry. I just realized when I was formatting my message I deleted a sentence in there.

This is what it should have said,

working on a code that needs to be able to search a specific range Sheet1.Range("N15:EM134") for Text only, Once found process the below actions:

• Align text to the right of the cell
• Increase the font size of the found text to size 15

Alternatively, I would need to do the same thing in the same range but for cells with numbers only:

• Align text to the center
• Decrease font size to 11
• *One exception, there will be cells with only numeric values that contain a dash (example: "1-5")

Any help on this would be greatly appreciated. This was the one piece of the puzzle that I am not really knowledgeable on doing.

I have come up with a quick solution that works but it is not ideal and super fast but it gets the job done.

'-----------------------------------------------------
'--- Resets formulas on Timeline
'-----------------------------------------------------
Sub ResetFormulas()

Dim ws1 As Worksheet
Dim cell As Range, DataTable As Range, Rng As Range, DataTable2 As Range

Set ws1 = Sheets("Timeline")
LastR = ws1.Range("C" & Rows.Count).End(3).Row     'last row of column O
LastCol = Split(ws1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
LastUsedCol = Split(ws1.Range("N15:" & LastCol & LastR & "").Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)

Application.ScreenUpdating = False

Set DataTable = ws1.Range("N15:" & LastCol & LastR) 'Sets the full Data Table
Set DataTable2 = ws1.Range("N15:" & LastUsedCol & LastR) 'Sets the full Data Table

  'Adjust cells with Text Only
        For Each cell In DataTable2
            If Application.WorksheetFunction.IsText(cell) = True Then
                cell.Font.Size = 16
                cell.Font.Bold = True
                cell.HorizontalAlignment = xlRight
            End If
        Next cell
        
        'Adjust the Episodes that have more than 1 at drop ("1-#")
        For Each cell In DataTable2
            If Application.WorksheetFunction.CountIf(cell, "1-*") > 0 Then
                cell.Font.Size = 11
                cell.Font.Bold = False
                cell.HorizontalAlignment = xlCenter
            End If
        Next cell
        
        'Adjust the Episodes that have numeric values only
        For Each cell In DataTable2
            If IsNumeric(cell) = True Then
                cell.Font.Size = 11
                cell.Font.Bold = False
                cell.HorizontalAlignment = xlCenter
            End If
        Next cell
        
Application.ScreenUpdating = True

    MsgBox prompt:="Table has been updated", Title:="Process Update"

End Sub