In VBA Given an input box value, write a program that changes the font to italic for cells in a range below the given value

Question

I've gotten fairly confused on this problem. The question as stated above asks you to write a program that gives an input box where you may enter a cutoff level. Once the cutoff level is given all values in the range on the sheet below that cutoff level need to be italicized. Where I get stuck is making the code select cells, not the whole range. Any suggestions?

Assignment

The file Sales.xlsx contains sales data of a firm for its different regions in a given month. Write a macro that italicizes and changes the font color to blue for the months Region 8 had sales less than a cutoff level provided by the user in an InputBox. For example, if the user enters 10000 in the InputBox, the macro should search for the months where sales in Region 8 are less than 10000. Then, the macro should turn cells E9, F9, I9, J9, K9, and L9 to italic with blue font

My attempt

Sub Homework7Problem2() 
' Keyboard Shortcut: Ctrl+b 
Dim Cutoff As String 
Cutoff = InputBox("Enter the cutoff level you would like to use", "CutoffLevel")
If Range("B2:M41").Cells < CutoffLevel Then 
    Range("B2:M41").Cells.Font.Italic = True 
    With Selection.Font 
        .Color = -4165632 
        .TintAndShade = 0 
    End With 
End If 
End Sub

Answer 1

Basically you'd need to loop over the cells and determine which ones meet the criteria (in this case, cells that are less than the cutoff). Once you have identified those cells, you can format them however you like. I prefer to put them in to a range variable and then format all of the identified cells all at once at the end which is much faster than formatting each identified cell individually as you find it. As you learn more VBA you'll find that this is considered good practice.

Here is the commented code:

Sub Homework7Problem2()

    'Declare Variables
    Dim rngSales As Range       'Range to store the Sales cells
    Dim SalesCell As Range      'Loop variable for rngSales
    Dim rngBelowTarget As Range 'Range to store cells that are below the target/cutoff
    Dim dTarget As Double       'Variable used to store the target/cutoff entered by the user

    'Define what cells to check
    Set rngSales = Range("B2:M41")

    'Reset formatting so that only cells below the new target will be formatted
    With rngSales
        .Font.Italic = False
        .Font.Color = vbBlack
    End With

    'Use Application.InputBox and set the Type parameter to 1 to force a number
    dTarget = Application.InputBox("Enter the cutoff level you would like to use:", "Cutoff Level", Type:=1)
    If dTarget = 0 Then Exit Sub   'User pressed cancel

    'Verify if there will be any matching cells
    If WorksheetFunction.CountIf(rngSales, "<" & dTarget) = 0 Then
        'If no matching cells, return error and exit the subroutine
        MsgBox "No months found to have sales less than [" & dTarget & "]", , "No Matches"
        Exit Sub
    End If

    'Loop though each cell in the range
    For Each SalesCell In rngSales.Cells
        If SalesCell.Value < dTarget Then
            'If a matching cell is found, add it to the rngBelowTarget variable
            If rngBelowTarget Is Nothing Then
                'This is the first cell found
                Set rngBelowTarget = SalesCell
            Else
                'This is for subsequent cells found, add them to the variable
                Set rngBelowTarget = Union(rngBelowTarget, SalesCell)
            End If
        End If
    Next SalesCell

    'Format cells that meet the condition
    With rngBelowTarget
        .Font.Italic = True
        .Font.Color = vbBlue
    End With

End Sub

Answer 2

There are a couple of ways you can do this, comparing the entire range to the cutoff-value as you've done won't work, but you can loop through the entire range and apply the formatting to any cell that fulfill the conditions:

Sub Homework7Problem2()
  ' Keyboard Shortcut: Ctrl+b
  Dim Cutoff As String
  Dim c As Range

  Cutoff = InputBox("Enter the cutoff level you would like to use", "CutoffLevel")

  For Each c In Range("B2:M41")
    If c < CLng(Cutoff) Then
      c.Font.Italic = True
      c.Font.Color = -4165632
      c.Font.TintAndShade = 0
    End If
  Next
End Sub

Alternately you could use a macro to apply somee conditional formatting to the range:

Sub Homework7Problem2()
  ' Keyboard Shortcut: Ctrl+b
  Dim Cutoff As String

  Cutoff = InputBox("Enter the cutoff level you would like to use", "CutoffLevel")

  With Range("B2:M41")
    ' First delete earlier conditional formatting, to avoid conflicts
    .FormatConditions.Delete
    ' Add new conditional formatting
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=" & Cutoff
    With .FormatConditions(1).Font
      .Italic = True
      .Color = -4165632
      .TintAndShade = 0
    End With
  End With
End Sub

I'd argue that it'd be much simpler to just apply the conditional formatting from the conditional formatting dialogue in Excel, but since the problem requires VBA, I suppose you may get some use out of this.